1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot._typing import E
  25from sqlglot.errors import ParseError
  26from sqlglot.helper import (
  27    AutoName,
  28    camel_to_snake_case,
  29    ensure_collection,
  30    ensure_list,
  31    seq_get,
  32    subclasses,
  33)
  34from sqlglot.tokens import Token
  35
  36if t.TYPE_CHECKING:
  37    from sqlglot.dialects.dialect import DialectType
  38
  39
  40class _Expression(type):
  41    def __new__(cls, clsname, bases, attrs):
  42        klass = super().__new__(cls, clsname, bases, attrs)
  43
  44        # When an Expression class is created, its key is automatically set to be
  45        # the lowercase version of the class' name.
  46        klass.key = clsname.lower()
  47
  48        # This is so that docstrings are not inherited in pdoc
  49        klass.__doc__ = klass.__doc__ or ""
  50
  51        return klass
  52
  53
  54class Expression(metaclass=_Expression):
  55    """
  56    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  57    context, such as its child expressions, their names (arg keys), and whether a given child expression
  58    is optional or not.
  59
  60    Attributes:
  61        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  62            and representing expressions as strings.
  63        arg_types: determines what arguments (child nodes) are supported by an expression. It
  64            maps arg keys to booleans that indicate whether the corresponding args are optional.
  65        parent: a reference to the parent expression (or None, in case of root expressions).
  66        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  67            uses to refer to it.
  68        comments: a list of comments that are associated with a given expression. This is used in
  69            order to preserve comments when transpiling SQL code.
  70        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  71            optimizer, in order to enable some transformations that require type information.
  72        meta: a dictionary that can be used to store useful metadata for a given expression.
  73
  74    Example:
  75        >>> class Foo(Expression):
  76        ...     arg_types = {"this": True, "expression": False}
  77
  78        The above definition informs us that Foo is an Expression that requires an argument called
  79        "this" and may also optionally receive an argument called "expression".
  80
  81    Args:
  82        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  83    """
  84
  85    key = "expression"
  86    arg_types = {"this": True}
  87    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  88
  89    def __init__(self, **args: t.Any):
  90        self.args: t.Dict[str, t.Any] = args
  91        self.parent: t.Optional[Expression] = None
  92        self.arg_key: t.Optional[str] = None
  93        self.comments: t.Optional[t.List[str]] = None
  94        self._type: t.Optional[DataType] = None
  95        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  96        self._hash: t.Optional[int] = None
  97
  98        for arg_key, value in self.args.items():
  99            self._set_parent(arg_key, value)
 100
 101    def __eq__(self, other) -> bool:
 102        return type(self) is type(other) and hash(self) == hash(other)
 103
 104    @property
 105    def hashable_args(self) -> t.Any:
 106        return frozenset(
 107            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 108            for k, v in self.args.items()
 109            if not (v is None or v is False or (type(v) is list and not v))
 110        )
 111
 112    def __hash__(self) -> int:
 113        if self._hash is not None:
 114            return self._hash
 115
 116        return hash((self.__class__, self.hashable_args))
 117
 118    @property
 119    def this(self):
 120        """
 121        Retrieves the argument with key "this".
 122        """
 123        return self.args.get("this")
 124
 125    @property
 126    def expression(self):
 127        """
 128        Retrieves the argument with key "expression".
 129        """
 130        return self.args.get("expression")
 131
 132    @property
 133    def expressions(self):
 134        """
 135        Retrieves the argument with key "expressions".
 136        """
 137        return self.args.get("expressions") or []
 138
 139    def text(self, key) -> str:
 140        """
 141        Returns a textual representation of the argument corresponding to "key". This can only be used
 142        for args that are strings or leaf Expression instances, such as identifiers and literals.
 143        """
 144        field = self.args.get(key)
 145        if isinstance(field, str):
 146            return field
 147        if isinstance(field, (Identifier, Literal, Var)):
 148            return field.this
 149        if isinstance(field, (Star, Null)):
 150            return field.name
 151        return ""
 152
 153    @property
 154    def is_string(self) -> bool:
 155        """
 156        Checks whether a Literal expression is a string.
 157        """
 158        return isinstance(self, Literal) and self.args["is_string"]
 159
 160    @property
 161    def is_number(self) -> bool:
 162        """
 163        Checks whether a Literal expression is a number.
 164        """
 165        return isinstance(self, Literal) and not self.args["is_string"]
 166
 167    @property
 168    def is_int(self) -> bool:
 169        """
 170        Checks whether a Literal expression is an integer.
 171        """
 172        if self.is_number:
 173            try:
 174                int(self.name)
 175                return True
 176            except ValueError:
 177                pass
 178        return False
 179
 180    @property
 181    def is_star(self) -> bool:
 182        """Checks whether an expression is a star."""
 183        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 184
 185    @property
 186    def alias(self) -> str:
 187        """
 188        Returns the alias of the expression, or an empty string if it's not aliased.
 189        """
 190        if isinstance(self.args.get("alias"), TableAlias):
 191            return self.args["alias"].name
 192        return self.text("alias")
 193
 194    @property
 195    def alias_column_names(self) -> t.List[str]:
 196        table_alias = self.args.get("alias")
 197        if not table_alias:
 198            return []
 199        return [c.name for c in table_alias.args.get("columns") or []]
 200
 201    @property
 202    def name(self) -> str:
 203        return self.text("this")
 204
 205    @property
 206    def alias_or_name(self) -> str:
 207        return self.alias or self.name
 208
 209    @property
 210    def output_name(self) -> str:
 211        """
 212        Name of the output column if this expression is a selection.
 213
 214        If the Expression has no output name, an empty string is returned.
 215
 216        Example:
 217            >>> from sqlglot import parse_one
 218            >>> parse_one("SELECT a").expressions[0].output_name
 219            'a'
 220            >>> parse_one("SELECT b AS c").expressions[0].output_name
 221            'c'
 222            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 223            ''
 224        """
 225        return ""
 226
 227    @property
 228    def type(self) -> t.Optional[DataType]:
 229        return self._type
 230
 231    @type.setter
 232    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 233        if dtype and not isinstance(dtype, DataType):
 234            dtype = DataType.build(dtype)
 235        self._type = dtype  # type: ignore
 236
 237    @property
 238    def meta(self) -> t.Dict[str, t.Any]:
 239        if self._meta is None:
 240            self._meta = {}
 241        return self._meta
 242
 243    def __deepcopy__(self, memo):
 244        copy = self.__class__(**deepcopy(self.args))
 245        if self.comments is not None:
 246            copy.comments = deepcopy(self.comments)
 247
 248        if self._type is not None:
 249            copy._type = self._type.copy()
 250
 251        if self._meta is not None:
 252            copy._meta = deepcopy(self._meta)
 253
 254        return copy
 255
 256    def copy(self):
 257        """
 258        Returns a deep copy of the expression.
 259        """
 260        new = deepcopy(self)
 261        new.parent = self.parent
 262        return new
 263
 264    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 265        if self.comments is None:
 266            self.comments = []
 267        if comments:
 268            self.comments.extend(comments)
 269
 270    def append(self, arg_key: str, value: t.Any) -> None:
 271        """
 272        Appends value to arg_key if it's a list or sets it as a new list.
 273
 274        Args:
 275            arg_key (str): name of the list expression arg
 276            value (Any): value to append to the list
 277        """
 278        if not isinstance(self.args.get(arg_key), list):
 279            self.args[arg_key] = []
 280        self.args[arg_key].append(value)
 281        self._set_parent(arg_key, value)
 282
 283    def set(self, arg_key: str, value: t.Any) -> None:
 284        """
 285        Sets arg_key to value.
 286
 287        Args:
 288            arg_key: name of the expression arg.
 289            value: value to set the arg to.
 290        """
 291        if value is None:
 292            self.args.pop(arg_key, None)
 293            return
 294
 295        self.args[arg_key] = value
 296        self._set_parent(arg_key, value)
 297
 298    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 299        if hasattr(value, "parent"):
 300            value.parent = self
 301            value.arg_key = arg_key
 302        elif type(value) is list:
 303            for v in value:
 304                if hasattr(v, "parent"):
 305                    v.parent = self
 306                    v.arg_key = arg_key
 307
 308    @property
 309    def depth(self) -> int:
 310        """
 311        Returns the depth of this tree.
 312        """
 313        if self.parent:
 314            return self.parent.depth + 1
 315        return 0
 316
 317    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 318        """Yields the key and expression for all arguments, exploding list args."""
 319        for k, vs in self.args.items():
 320            if type(vs) is list:
 321                for v in vs:
 322                    if hasattr(v, "parent"):
 323                        yield k, v
 324            else:
 325                if hasattr(vs, "parent"):
 326                    yield k, vs
 327
 328    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 329        """
 330        Returns the first node in this tree which matches at least one of
 331        the specified types.
 332
 333        Args:
 334            expression_types: the expression type(s) to match.
 335            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 336
 337        Returns:
 338            The node which matches the criteria or None if no such node was found.
 339        """
 340        return next(self.find_all(*expression_types, bfs=bfs), None)
 341
 342    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 343        """
 344        Returns a generator object which visits all nodes in this tree and only
 345        yields those that match at least one of the specified expression types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 350
 351        Returns:
 352            The generator object.
 353        """
 354        for expression, *_ in self.walk(bfs=bfs):
 355            if isinstance(expression, expression_types):
 356                yield expression
 357
 358    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 359        """
 360        Returns a nearest parent matching expression_types.
 361
 362        Args:
 363            expression_types: the expression type(s) to match.
 364
 365        Returns:
 366            The parent node.
 367        """
 368        ancestor = self.parent
 369        while ancestor and not isinstance(ancestor, expression_types):
 370            ancestor = ancestor.parent
 371        return t.cast(E, ancestor)
 372
 373    @property
 374    def parent_select(self) -> t.Optional[Select]:
 375        """
 376        Returns the parent select statement.
 377        """
 378        return self.find_ancestor(Select)
 379
 380    @property
 381    def same_parent(self) -> bool:
 382        """Returns if the parent is the same class as itself."""
 383        return type(self.parent) is self.__class__
 384
 385    def root(self) -> Expression:
 386        """
 387        Returns the root expression of this tree.
 388        """
 389        expression = self
 390        while expression.parent:
 391            expression = expression.parent
 392        return expression
 393
 394    def walk(self, bfs=True, prune=None):
 395        """
 396        Returns a generator object which visits all nodes in this tree.
 397
 398        Args:
 399            bfs (bool): if set to True the BFS traversal order will be applied,
 400                otherwise the DFS traversal will be used instead.
 401            prune ((node, parent, arg_key) -> bool): callable that returns True if
 402                the generator should stop traversing this branch of the tree.
 403
 404        Returns:
 405            the generator object.
 406        """
 407        if bfs:
 408            yield from self.bfs(prune=prune)
 409        else:
 410            yield from self.dfs(prune=prune)
 411
 412    def dfs(self, parent=None, key=None, prune=None):
 413        """
 414        Returns a generator object which visits all nodes in this tree in
 415        the DFS (Depth-first) order.
 416
 417        Returns:
 418            The generator object.
 419        """
 420        parent = parent or self.parent
 421        yield self, parent, key
 422        if prune and prune(self, parent, key):
 423            return
 424
 425        for k, v in self.iter_expressions():
 426            yield from v.dfs(self, k, prune)
 427
 428    def bfs(self, prune=None):
 429        """
 430        Returns a generator object which visits all nodes in this tree in
 431        the BFS (Breadth-first) order.
 432
 433        Returns:
 434            The generator object.
 435        """
 436        queue = deque([(self, self.parent, None)])
 437
 438        while queue:
 439            item, parent, key = queue.popleft()
 440
 441            yield item, parent, key
 442            if prune and prune(item, parent, key):
 443                continue
 444
 445            for k, v in item.iter_expressions():
 446                queue.append((v, item, k))
 447
 448    def unnest(self):
 449        """
 450        Returns the first non parenthesis child or self.
 451        """
 452        expression = self
 453        while type(expression) is Paren:
 454            expression = expression.this
 455        return expression
 456
 457    def unalias(self):
 458        """
 459        Returns the inner expression if this is an Alias.
 460        """
 461        if isinstance(self, Alias):
 462            return self.this
 463        return self
 464
 465    def unnest_operands(self):
 466        """
 467        Returns unnested operands as a tuple.
 468        """
 469        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 470
 471    def flatten(self, unnest=True):
 472        """
 473        Returns a generator which yields child nodes who's parents are the same class.
 474
 475        A AND B AND C -> [A, B, C]
 476        """
 477        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 478            if not type(node) is self.__class__:
 479                yield node.unnest() if unnest else node
 480
 481    def __str__(self) -> str:
 482        return self.sql()
 483
 484    def __repr__(self) -> str:
 485        return self._to_s()
 486
 487    def sql(self, dialect: DialectType = None, **opts) -> str:
 488        """
 489        Returns SQL string representation of this tree.
 490
 491        Args:
 492            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 493            opts: other `sqlglot.generator.Generator` options.
 494
 495        Returns:
 496            The SQL string.
 497        """
 498        from sqlglot.dialects import Dialect
 499
 500        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 501
 502    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 503        indent = "" if not level else "\n"
 504        indent += "".join(["  "] * level)
 505        left = f"({self.key.upper()} "
 506
 507        args: t.Dict[str, t.Any] = {
 508            k: ", ".join(
 509                v._to_s(hide_missing=hide_missing, level=level + 1)
 510                if hasattr(v, "_to_s")
 511                else str(v)
 512                for v in ensure_list(vs)
 513                if v is not None
 514            )
 515            for k, vs in self.args.items()
 516        }
 517        args["comments"] = self.comments
 518        args["type"] = self.type
 519        args = {k: v for k, v in args.items() if v or not hide_missing}
 520
 521        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 522        right += ")"
 523
 524        return indent + left + right
 525
 526    def transform(self, fun, *args, copy=True, **kwargs):
 527        """
 528        Recursively visits all tree nodes (excluding already transformed ones)
 529        and applies the given transformation function to each node.
 530
 531        Args:
 532            fun (function): a function which takes a node as an argument and returns a
 533                new transformed node or the same node without modifications. If the function
 534                returns None, then the corresponding node will be removed from the syntax tree.
 535            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 536                modified in place.
 537
 538        Returns:
 539            The transformed tree.
 540        """
 541        node = self.copy() if copy else self
 542        new_node = fun(node, *args, **kwargs)
 543
 544        if new_node is None or not isinstance(new_node, Expression):
 545            return new_node
 546        if new_node is not node:
 547            new_node.parent = node.parent
 548            return new_node
 549
 550        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 551        return new_node
 552
 553    @t.overload
 554    def replace(self, expression: E) -> E:
 555        ...
 556
 557    @t.overload
 558    def replace(self, expression: None) -> None:
 559        ...
 560
 561    def replace(self, expression):
 562        """
 563        Swap out this expression with a new expression.
 564
 565        For example::
 566
 567            >>> tree = Select().select("x").from_("tbl")
 568            >>> tree.find(Column).replace(Column(this="y"))
 569            (COLUMN this: y)
 570            >>> tree.sql()
 571            'SELECT y FROM tbl'
 572
 573        Args:
 574            expression: new node
 575
 576        Returns:
 577            The new expression or expressions.
 578        """
 579        if not self.parent:
 580            return expression
 581
 582        parent = self.parent
 583        self.parent = None
 584
 585        replace_children(parent, lambda child: expression if child is self else child)
 586        return expression
 587
 588    def pop(self: E) -> E:
 589        """
 590        Remove this expression from its AST.
 591
 592        Returns:
 593            The popped expression.
 594        """
 595        self.replace(None)
 596        return self
 597
 598    def assert_is(self, type_: t.Type[E]) -> E:
 599        """
 600        Assert that this `Expression` is an instance of `type_`.
 601
 602        If it is NOT an instance of `type_`, this raises an assertion error.
 603        Otherwise, this returns this expression.
 604
 605        Examples:
 606            This is useful for type security in chained expressions:
 607
 608            >>> import sqlglot
 609            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 610            'SELECT x, z FROM y'
 611        """
 612        assert isinstance(self, type_)
 613        return self
 614
 615    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 616        """
 617        Checks if this expression is valid (e.g. all mandatory args are set).
 618
 619        Args:
 620            args: a sequence of values that were used to instantiate a Func expression. This is used
 621                to check that the provided arguments don't exceed the function argument limit.
 622
 623        Returns:
 624            A list of error messages for all possible errors that were found.
 625        """
 626        errors: t.List[str] = []
 627
 628        for k in self.args:
 629            if k not in self.arg_types:
 630                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 631        for k, mandatory in self.arg_types.items():
 632            v = self.args.get(k)
 633            if mandatory and (v is None or (isinstance(v, list) and not v)):
 634                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 635
 636        if (
 637            args
 638            and isinstance(self, Func)
 639            and len(args) > len(self.arg_types)
 640            and not self.is_var_len_args
 641        ):
 642            errors.append(
 643                f"The number of provided arguments ({len(args)}) is greater than "
 644                f"the maximum number of supported arguments ({len(self.arg_types)})"
 645            )
 646
 647        return errors
 648
 649    def dump(self):
 650        """
 651        Dump this Expression to a JSON-serializable dict.
 652        """
 653        from sqlglot.serde import dump
 654
 655        return dump(self)
 656
 657    @classmethod
 658    def load(cls, obj):
 659        """
 660        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 661        """
 662        from sqlglot.serde import load
 663
 664        return load(obj)
 665
 666
 667IntoType = t.Union[
 668    str,
 669    t.Type[Expression],
 670    t.Collection[t.Union[str, t.Type[Expression]]],
 671]
 672ExpOrStr = t.Union[str, Expression]
 673
 674
 675class Condition(Expression):
 676    def and_(
 677        self,
 678        *expressions: t.Optional[ExpOrStr],
 679        dialect: DialectType = None,
 680        copy: bool = True,
 681        **opts,
 682    ) -> Condition:
 683        """
 684        AND this condition with one or multiple expressions.
 685
 686        Example:
 687            >>> condition("x=1").and_("y=1").sql()
 688            'x = 1 AND y = 1'
 689
 690        Args:
 691            *expressions: the SQL code strings to parse.
 692                If an `Expression` instance is passed, it will be used as-is.
 693            dialect: the dialect used to parse the input expression.
 694            copy: whether or not to copy the involved expressions (only applies to Expressions).
 695            opts: other options to use to parse the input expressions.
 696
 697        Returns:
 698            The new And condition.
 699        """
 700        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 701
 702    def or_(
 703        self,
 704        *expressions: t.Optional[ExpOrStr],
 705        dialect: DialectType = None,
 706        copy: bool = True,
 707        **opts,
 708    ) -> Condition:
 709        """
 710        OR this condition with one or multiple expressions.
 711
 712        Example:
 713            >>> condition("x=1").or_("y=1").sql()
 714            'x = 1 OR y = 1'
 715
 716        Args:
 717            *expressions: the SQL code strings to parse.
 718                If an `Expression` instance is passed, it will be used as-is.
 719            dialect: the dialect used to parse the input expression.
 720            copy: whether or not to copy the involved expressions (only applies to Expressions).
 721            opts: other options to use to parse the input expressions.
 722
 723        Returns:
 724            The new Or condition.
 725        """
 726        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 727
 728    def not_(self, copy: bool = True):
 729        """
 730        Wrap this condition with NOT.
 731
 732        Example:
 733            >>> condition("x=1").not_().sql()
 734            'NOT x = 1'
 735
 736        Args:
 737            copy: whether or not to copy this object.
 738
 739        Returns:
 740            The new Not instance.
 741        """
 742        return not_(self, copy=copy)
 743
 744    def as_(
 745        self,
 746        alias: str | Identifier,
 747        quoted: t.Optional[bool] = None,
 748        dialect: DialectType = None,
 749        copy: bool = True,
 750        **opts,
 751    ) -> Alias:
 752        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 753
 754    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 755        this = self.copy()
 756        other = convert(other, copy=True)
 757        if not isinstance(this, klass) and not isinstance(other, klass):
 758            this = _wrap(this, Binary)
 759            other = _wrap(other, Binary)
 760        if reverse:
 761            return klass(this=other, expression=this)
 762        return klass(this=this, expression=other)
 763
 764    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]):
 765        return Bracket(
 766            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 767        )
 768
 769    def isin(
 770        self,
 771        *expressions: t.Any,
 772        query: t.Optional[ExpOrStr] = None,
 773        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 774        copy: bool = True,
 775        **opts,
 776    ) -> In:
 777        return In(
 778            this=maybe_copy(self, copy),
 779            expressions=[convert(e, copy=copy) for e in expressions],
 780            query=maybe_parse(query, copy=copy, **opts) if query else None,
 781            unnest=Unnest(
 782                expressions=[
 783                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 784                ]
 785            )
 786            if unnest
 787            else None,
 788        )
 789
 790    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 791        return Between(
 792            this=maybe_copy(self, copy),
 793            low=convert(low, copy=copy, **opts),
 794            high=convert(high, copy=copy, **opts),
 795        )
 796
 797    def is_(self, other: ExpOrStr) -> Is:
 798        return self._binop(Is, other)
 799
 800    def like(self, other: ExpOrStr) -> Like:
 801        return self._binop(Like, other)
 802
 803    def ilike(self, other: ExpOrStr) -> ILike:
 804        return self._binop(ILike, other)
 805
 806    def eq(self, other: t.Any) -> EQ:
 807        return self._binop(EQ, other)
 808
 809    def neq(self, other: t.Any) -> NEQ:
 810        return self._binop(NEQ, other)
 811
 812    def rlike(self, other: ExpOrStr) -> RegexpLike:
 813        return self._binop(RegexpLike, other)
 814
 815    def __lt__(self, other: t.Any) -> LT:
 816        return self._binop(LT, other)
 817
 818    def __le__(self, other: t.Any) -> LTE:
 819        return self._binop(LTE, other)
 820
 821    def __gt__(self, other: t.Any) -> GT:
 822        return self._binop(GT, other)
 823
 824    def __ge__(self, other: t.Any) -> GTE:
 825        return self._binop(GTE, other)
 826
 827    def __add__(self, other: t.Any) -> Add:
 828        return self._binop(Add, other)
 829
 830    def __radd__(self, other: t.Any) -> Add:
 831        return self._binop(Add, other, reverse=True)
 832
 833    def __sub__(self, other: t.Any) -> Sub:
 834        return self._binop(Sub, other)
 835
 836    def __rsub__(self, other: t.Any) -> Sub:
 837        return self._binop(Sub, other, reverse=True)
 838
 839    def __mul__(self, other: t.Any) -> Mul:
 840        return self._binop(Mul, other)
 841
 842    def __rmul__(self, other: t.Any) -> Mul:
 843        return self._binop(Mul, other, reverse=True)
 844
 845    def __truediv__(self, other: t.Any) -> Div:
 846        return self._binop(Div, other)
 847
 848    def __rtruediv__(self, other: t.Any) -> Div:
 849        return self._binop(Div, other, reverse=True)
 850
 851    def __floordiv__(self, other: t.Any) -> IntDiv:
 852        return self._binop(IntDiv, other)
 853
 854    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 855        return self._binop(IntDiv, other, reverse=True)
 856
 857    def __mod__(self, other: t.Any) -> Mod:
 858        return self._binop(Mod, other)
 859
 860    def __rmod__(self, other: t.Any) -> Mod:
 861        return self._binop(Mod, other, reverse=True)
 862
 863    def __pow__(self, other: t.Any) -> Pow:
 864        return self._binop(Pow, other)
 865
 866    def __rpow__(self, other: t.Any) -> Pow:
 867        return self._binop(Pow, other, reverse=True)
 868
 869    def __and__(self, other: t.Any) -> And:
 870        return self._binop(And, other)
 871
 872    def __rand__(self, other: t.Any) -> And:
 873        return self._binop(And, other, reverse=True)
 874
 875    def __or__(self, other: t.Any) -> Or:
 876        return self._binop(Or, other)
 877
 878    def __ror__(self, other: t.Any) -> Or:
 879        return self._binop(Or, other, reverse=True)
 880
 881    def __neg__(self) -> Neg:
 882        return Neg(this=_wrap(self.copy(), Binary))
 883
 884    def __invert__(self) -> Not:
 885        return not_(self.copy())
 886
 887
 888class Predicate(Condition):
 889    """Relationships like x = y, x > 1, x >= y."""
 890
 891
 892class DerivedTable(Expression):
 893    @property
 894    def selects(self) -> t.List[Expression]:
 895        return self.this.selects if isinstance(self.this, Subqueryable) else []
 896
 897    @property
 898    def named_selects(self) -> t.List[str]:
 899        return [select.output_name for select in self.selects]
 900
 901
 902class Unionable(Expression):
 903    def union(
 904        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 905    ) -> Unionable:
 906        """
 907        Builds a UNION expression.
 908
 909        Example:
 910            >>> import sqlglot
 911            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 912            'SELECT * FROM foo UNION SELECT * FROM bla'
 913
 914        Args:
 915            expression: the SQL code string.
 916                If an `Expression` instance is passed, it will be used as-is.
 917            distinct: set the DISTINCT flag if and only if this is true.
 918            dialect: the dialect used to parse the input expression.
 919            opts: other options to use to parse the input expressions.
 920
 921        Returns:
 922            The new Union expression.
 923        """
 924        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 925
 926    def intersect(
 927        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 928    ) -> Unionable:
 929        """
 930        Builds an INTERSECT expression.
 931
 932        Example:
 933            >>> import sqlglot
 934            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 935            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 936
 937        Args:
 938            expression: the SQL code string.
 939                If an `Expression` instance is passed, it will be used as-is.
 940            distinct: set the DISTINCT flag if and only if this is true.
 941            dialect: the dialect used to parse the input expression.
 942            opts: other options to use to parse the input expressions.
 943
 944        Returns:
 945            The new Intersect expression.
 946        """
 947        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 948
 949    def except_(
 950        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 951    ) -> Unionable:
 952        """
 953        Builds an EXCEPT expression.
 954
 955        Example:
 956            >>> import sqlglot
 957            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 958            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 959
 960        Args:
 961            expression: the SQL code string.
 962                If an `Expression` instance is passed, it will be used as-is.
 963            distinct: set the DISTINCT flag if and only if this is true.
 964            dialect: the dialect used to parse the input expression.
 965            opts: other options to use to parse the input expressions.
 966
 967        Returns:
 968            The new Except expression.
 969        """
 970        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 971
 972
 973class UDTF(DerivedTable, Unionable):
 974    @property
 975    def selects(self) -> t.List[Expression]:
 976        alias = self.args.get("alias")
 977        return alias.columns if alias else []
 978
 979
 980class Cache(Expression):
 981    arg_types = {
 982        "with": False,
 983        "this": True,
 984        "lazy": False,
 985        "options": False,
 986        "expression": False,
 987    }
 988
 989
 990class Uncache(Expression):
 991    arg_types = {"this": True, "exists": False}
 992
 993
 994class DDL(Expression):
 995    @property
 996    def ctes(self):
 997        with_ = self.args.get("with")
 998        if not with_:
 999            return []
1000        return with_.expressions
1001
1002    @property
1003    def named_selects(self) -> t.List[str]:
1004        if isinstance(self.expression, Subqueryable):
1005            return self.expression.named_selects
1006        return []
1007
1008    @property
1009    def selects(self) -> t.List[Expression]:
1010        if isinstance(self.expression, Subqueryable):
1011            return self.expression.selects
1012        return []
1013
1014
1015class Create(DDL):
1016    arg_types = {
1017        "with": False,
1018        "this": True,
1019        "kind": True,
1020        "expression": False,
1021        "exists": False,
1022        "properties": False,
1023        "replace": False,
1024        "unique": False,
1025        "indexes": False,
1026        "no_schema_binding": False,
1027        "begin": False,
1028        "clone": False,
1029    }
1030
1031
1032# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1033class Clone(Expression):
1034    arg_types = {
1035        "this": True,
1036        "when": False,
1037        "kind": False,
1038        "expression": False,
1039    }
1040
1041
1042class Describe(Expression):
1043    arg_types = {"this": True, "kind": False}
1044
1045
1046class Pragma(Expression):
1047    pass
1048
1049
1050class Set(Expression):
1051    arg_types = {"expressions": False, "unset": False, "tag": False}
1052
1053
1054class SetItem(Expression):
1055    arg_types = {
1056        "this": False,
1057        "expressions": False,
1058        "kind": False,
1059        "collate": False,  # MySQL SET NAMES statement
1060        "global": False,
1061    }
1062
1063
1064class Show(Expression):
1065    arg_types = {
1066        "this": True,
1067        "target": False,
1068        "offset": False,
1069        "limit": False,
1070        "like": False,
1071        "where": False,
1072        "db": False,
1073        "full": False,
1074        "mutex": False,
1075        "query": False,
1076        "channel": False,
1077        "global": False,
1078        "log": False,
1079        "position": False,
1080        "types": False,
1081    }
1082
1083
1084class UserDefinedFunction(Expression):
1085    arg_types = {"this": True, "expressions": False, "wrapped": False}
1086
1087
1088class CharacterSet(Expression):
1089    arg_types = {"this": True, "default": False}
1090
1091
1092class With(Expression):
1093    arg_types = {"expressions": True, "recursive": False}
1094
1095    @property
1096    def recursive(self) -> bool:
1097        return bool(self.args.get("recursive"))
1098
1099
1100class WithinGroup(Expression):
1101    arg_types = {"this": True, "expression": False}
1102
1103
1104class CTE(DerivedTable):
1105    arg_types = {"this": True, "alias": True}
1106
1107
1108class TableAlias(Expression):
1109    arg_types = {"this": False, "columns": False}
1110
1111    @property
1112    def columns(self):
1113        return self.args.get("columns") or []
1114
1115
1116class BitString(Condition):
1117    pass
1118
1119
1120class HexString(Condition):
1121    pass
1122
1123
1124class ByteString(Condition):
1125    pass
1126
1127
1128class RawString(Condition):
1129    pass
1130
1131
1132class Column(Condition):
1133    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1134
1135    @property
1136    def table(self) -> str:
1137        return self.text("table")
1138
1139    @property
1140    def db(self) -> str:
1141        return self.text("db")
1142
1143    @property
1144    def catalog(self) -> str:
1145        return self.text("catalog")
1146
1147    @property
1148    def output_name(self) -> str:
1149        return self.name
1150
1151    @property
1152    def parts(self) -> t.List[Identifier]:
1153        """Return the parts of a column in order catalog, db, table, name."""
1154        return [
1155            t.cast(Identifier, self.args[part])
1156            for part in ("catalog", "db", "table", "this")
1157            if self.args.get(part)
1158        ]
1159
1160    def to_dot(self) -> Dot:
1161        """Converts the column into a dot expression."""
1162        parts = self.parts
1163        parent = self.parent
1164
1165        while parent:
1166            if isinstance(parent, Dot):
1167                parts.append(parent.expression)
1168            parent = parent.parent
1169
1170        return Dot.build(parts)
1171
1172
1173class ColumnPosition(Expression):
1174    arg_types = {"this": False, "position": True}
1175
1176
1177class ColumnDef(Expression):
1178    arg_types = {
1179        "this": True,
1180        "kind": False,
1181        "constraints": False,
1182        "exists": False,
1183        "position": False,
1184    }
1185
1186    @property
1187    def constraints(self) -> t.List[ColumnConstraint]:
1188        return self.args.get("constraints") or []
1189
1190
1191class AlterColumn(Expression):
1192    arg_types = {
1193        "this": True,
1194        "dtype": False,
1195        "collate": False,
1196        "using": False,
1197        "default": False,
1198        "drop": False,
1199    }
1200
1201
1202class RenameTable(Expression):
1203    pass
1204
1205
1206class Comment(Expression):
1207    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1208
1209
1210# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1211class MergeTreeTTLAction(Expression):
1212    arg_types = {
1213        "this": True,
1214        "delete": False,
1215        "recompress": False,
1216        "to_disk": False,
1217        "to_volume": False,
1218    }
1219
1220
1221# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1222class MergeTreeTTL(Expression):
1223    arg_types = {
1224        "expressions": True,
1225        "where": False,
1226        "group": False,
1227        "aggregates": False,
1228    }
1229
1230
1231# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1232class IndexConstraintOption(Expression):
1233    arg_types = {
1234        "key_block_size": False,
1235        "using": False,
1236        "parser": False,
1237        "comment": False,
1238        "visible": False,
1239        "engine_attr": False,
1240        "secondary_engine_attr": False,
1241    }
1242
1243
1244class ColumnConstraint(Expression):
1245    arg_types = {"this": False, "kind": True}
1246
1247    @property
1248    def kind(self) -> ColumnConstraintKind:
1249        return self.args["kind"]
1250
1251
1252class ColumnConstraintKind(Expression):
1253    pass
1254
1255
1256class AutoIncrementColumnConstraint(ColumnConstraintKind):
1257    pass
1258
1259
1260class CaseSpecificColumnConstraint(ColumnConstraintKind):
1261    arg_types = {"not_": True}
1262
1263
1264class CharacterSetColumnConstraint(ColumnConstraintKind):
1265    arg_types = {"this": True}
1266
1267
1268class CheckColumnConstraint(ColumnConstraintKind):
1269    pass
1270
1271
1272class ClusteredColumnConstraint(ColumnConstraintKind):
1273    pass
1274
1275
1276class CollateColumnConstraint(ColumnConstraintKind):
1277    pass
1278
1279
1280class CommentColumnConstraint(ColumnConstraintKind):
1281    pass
1282
1283
1284class CompressColumnConstraint(ColumnConstraintKind):
1285    pass
1286
1287
1288class DateFormatColumnConstraint(ColumnConstraintKind):
1289    arg_types = {"this": True}
1290
1291
1292class DefaultColumnConstraint(ColumnConstraintKind):
1293    pass
1294
1295
1296class EncodeColumnConstraint(ColumnConstraintKind):
1297    pass
1298
1299
1300class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1301    # this: True -> ALWAYS, this: False -> BY DEFAULT
1302    arg_types = {
1303        "this": False,
1304        "expression": False,
1305        "on_null": False,
1306        "start": False,
1307        "increment": False,
1308        "minvalue": False,
1309        "maxvalue": False,
1310        "cycle": False,
1311    }
1312
1313
1314# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1315class IndexColumnConstraint(ColumnConstraintKind):
1316    arg_types = {"this": False, "schema": True, "kind": False, "type": False, "options": False}
1317
1318
1319class InlineLengthColumnConstraint(ColumnConstraintKind):
1320    pass
1321
1322
1323class NonClusteredColumnConstraint(ColumnConstraintKind):
1324    pass
1325
1326
1327class NotForReplicationColumnConstraint(ColumnConstraintKind):
1328    arg_types = {}
1329
1330
1331class NotNullColumnConstraint(ColumnConstraintKind):
1332    arg_types = {"allow_null": False}
1333
1334
1335# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1336class OnUpdateColumnConstraint(ColumnConstraintKind):
1337    pass
1338
1339
1340class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1341    arg_types = {"desc": False}
1342
1343
1344class TitleColumnConstraint(ColumnConstraintKind):
1345    pass
1346
1347
1348class UniqueColumnConstraint(ColumnConstraintKind):
1349    arg_types = {"this": False}
1350
1351
1352class UppercaseColumnConstraint(ColumnConstraintKind):
1353    arg_types: t.Dict[str, t.Any] = {}
1354
1355
1356class PathColumnConstraint(ColumnConstraintKind):
1357    pass
1358
1359
1360# computed column expression
1361# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1362class ComputedColumnConstraint(ColumnConstraintKind):
1363    arg_types = {"this": True, "persisted": False, "not_null": False}
1364
1365
1366class Constraint(Expression):
1367    arg_types = {"this": True, "expressions": True}
1368
1369
1370class Delete(Expression):
1371    arg_types = {
1372        "with": False,
1373        "this": False,
1374        "using": False,
1375        "where": False,
1376        "returning": False,
1377        "limit": False,
1378        "tables": False,  # Multiple-Table Syntax (MySQL)
1379    }
1380
1381    def delete(
1382        self,
1383        table: ExpOrStr,
1384        dialect: DialectType = None,
1385        copy: bool = True,
1386        **opts,
1387    ) -> Delete:
1388        """
1389        Create a DELETE expression or replace the table on an existing DELETE expression.
1390
1391        Example:
1392            >>> delete("tbl").sql()
1393            'DELETE FROM tbl'
1394
1395        Args:
1396            table: the table from which to delete.
1397            dialect: the dialect used to parse the input expression.
1398            copy: if `False`, modify this expression instance in-place.
1399            opts: other options to use to parse the input expressions.
1400
1401        Returns:
1402            Delete: the modified expression.
1403        """
1404        return _apply_builder(
1405            expression=table,
1406            instance=self,
1407            arg="this",
1408            dialect=dialect,
1409            into=Table,
1410            copy=copy,
1411            **opts,
1412        )
1413
1414    def where(
1415        self,
1416        *expressions: t.Optional[ExpOrStr],
1417        append: bool = True,
1418        dialect: DialectType = None,
1419        copy: bool = True,
1420        **opts,
1421    ) -> Delete:
1422        """
1423        Append to or set the WHERE expressions.
1424
1425        Example:
1426            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1427            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1428
1429        Args:
1430            *expressions: the SQL code strings to parse.
1431                If an `Expression` instance is passed, it will be used as-is.
1432                Multiple expressions are combined with an AND operator.
1433            append: if `True`, AND the new expressions to any existing expression.
1434                Otherwise, this resets the expression.
1435            dialect: the dialect used to parse the input expressions.
1436            copy: if `False`, modify this expression instance in-place.
1437            opts: other options to use to parse the input expressions.
1438
1439        Returns:
1440            Delete: the modified expression.
1441        """
1442        return _apply_conjunction_builder(
1443            *expressions,
1444            instance=self,
1445            arg="where",
1446            append=append,
1447            into=Where,
1448            dialect=dialect,
1449            copy=copy,
1450            **opts,
1451        )
1452
1453    def returning(
1454        self,
1455        expression: ExpOrStr,
1456        dialect: DialectType = None,
1457        copy: bool = True,
1458        **opts,
1459    ) -> Delete:
1460        """
1461        Set the RETURNING expression. Not supported by all dialects.
1462
1463        Example:
1464            >>> delete("tbl").returning("*", dialect="postgres").sql()
1465            'DELETE FROM tbl RETURNING *'
1466
1467        Args:
1468            expression: the SQL code strings to parse.
1469                If an `Expression` instance is passed, it will be used as-is.
1470            dialect: the dialect used to parse the input expressions.
1471            copy: if `False`, modify this expression instance in-place.
1472            opts: other options to use to parse the input expressions.
1473
1474        Returns:
1475            Delete: the modified expression.
1476        """
1477        return _apply_builder(
1478            expression=expression,
1479            instance=self,
1480            arg="returning",
1481            prefix="RETURNING",
1482            dialect=dialect,
1483            copy=copy,
1484            into=Returning,
1485            **opts,
1486        )
1487
1488
1489class Drop(Expression):
1490    arg_types = {
1491        "this": False,
1492        "kind": False,
1493        "exists": False,
1494        "temporary": False,
1495        "materialized": False,
1496        "cascade": False,
1497        "constraints": False,
1498        "purge": False,
1499    }
1500
1501
1502class Filter(Expression):
1503    arg_types = {"this": True, "expression": True}
1504
1505
1506class Check(Expression):
1507    pass
1508
1509
1510# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1511class Connect(Expression):
1512    arg_types = {"start": False, "connect": True}
1513
1514
1515class Prior(Expression):
1516    pass
1517
1518
1519class Directory(Expression):
1520    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1521    arg_types = {"this": True, "local": False, "row_format": False}
1522
1523
1524class ForeignKey(Expression):
1525    arg_types = {
1526        "expressions": True,
1527        "reference": False,
1528        "delete": False,
1529        "update": False,
1530    }
1531
1532
1533class PrimaryKey(Expression):
1534    arg_types = {"expressions": True, "options": False}
1535
1536
1537# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1538# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1539class Into(Expression):
1540    arg_types = {"this": True, "temporary": False, "unlogged": False}
1541
1542
1543class From(Expression):
1544    @property
1545    def name(self) -> str:
1546        return self.this.name
1547
1548    @property
1549    def alias_or_name(self) -> str:
1550        return self.this.alias_or_name
1551
1552
1553class Having(Expression):
1554    pass
1555
1556
1557class Hint(Expression):
1558    arg_types = {"expressions": True}
1559
1560
1561class JoinHint(Expression):
1562    arg_types = {"this": True, "expressions": True}
1563
1564
1565class Identifier(Expression):
1566    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1567
1568    @property
1569    def quoted(self) -> bool:
1570        return bool(self.args.get("quoted"))
1571
1572    @property
1573    def hashable_args(self) -> t.Any:
1574        return (self.this, self.quoted)
1575
1576    @property
1577    def output_name(self) -> str:
1578        return self.name
1579
1580
1581class Index(Expression):
1582    arg_types = {
1583        "this": False,
1584        "table": False,
1585        "using": False,
1586        "where": False,
1587        "columns": False,
1588        "unique": False,
1589        "primary": False,
1590        "amp": False,  # teradata
1591        "partition_by": False,  # teradata
1592    }
1593
1594
1595class Insert(DDL):
1596    arg_types = {
1597        "with": False,
1598        "this": True,
1599        "expression": False,
1600        "conflict": False,
1601        "returning": False,
1602        "overwrite": False,
1603        "exists": False,
1604        "partition": False,
1605        "alternative": False,
1606        "where": False,
1607        "ignore": False,
1608    }
1609
1610    def with_(
1611        self,
1612        alias: ExpOrStr,
1613        as_: ExpOrStr,
1614        recursive: t.Optional[bool] = None,
1615        append: bool = True,
1616        dialect: DialectType = None,
1617        copy: bool = True,
1618        **opts,
1619    ) -> Insert:
1620        """
1621        Append to or set the common table expressions.
1622
1623        Example:
1624            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1625            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1626
1627        Args:
1628            alias: the SQL code string to parse as the table name.
1629                If an `Expression` instance is passed, this is used as-is.
1630            as_: the SQL code string to parse as the table expression.
1631                If an `Expression` instance is passed, it will be used as-is.
1632            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1633            append: if `True`, add to any existing expressions.
1634                Otherwise, this resets the expressions.
1635            dialect: the dialect used to parse the input expression.
1636            copy: if `False`, modify this expression instance in-place.
1637            opts: other options to use to parse the input expressions.
1638
1639        Returns:
1640            The modified expression.
1641        """
1642        return _apply_cte_builder(
1643            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1644        )
1645
1646
1647class OnConflict(Expression):
1648    arg_types = {
1649        "duplicate": False,
1650        "expressions": False,
1651        "nothing": False,
1652        "key": False,
1653        "constraint": False,
1654    }
1655
1656
1657class Returning(Expression):
1658    arg_types = {"expressions": True, "into": False}
1659
1660
1661# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1662class Introducer(Expression):
1663    arg_types = {"this": True, "expression": True}
1664
1665
1666# national char, like n'utf8'
1667class National(Expression):
1668    pass
1669
1670
1671class LoadData(Expression):
1672    arg_types = {
1673        "this": True,
1674        "local": False,
1675        "overwrite": False,
1676        "inpath": True,
1677        "partition": False,
1678        "input_format": False,
1679        "serde": False,
1680    }
1681
1682
1683class Partition(Expression):
1684    arg_types = {"expressions": True}
1685
1686
1687class Fetch(Expression):
1688    arg_types = {
1689        "direction": False,
1690        "count": False,
1691        "percent": False,
1692        "with_ties": False,
1693    }
1694
1695
1696class Group(Expression):
1697    arg_types = {
1698        "expressions": False,
1699        "grouping_sets": False,
1700        "cube": False,
1701        "rollup": False,
1702        "totals": False,
1703        "all": False,
1704    }
1705
1706
1707class Lambda(Expression):
1708    arg_types = {"this": True, "expressions": True}
1709
1710
1711class Limit(Expression):
1712    arg_types = {"this": False, "expression": True, "offset": False}
1713
1714
1715class Literal(Condition):
1716    arg_types = {"this": True, "is_string": True}
1717
1718    @property
1719    def hashable_args(self) -> t.Any:
1720        return (self.this, self.args.get("is_string"))
1721
1722    @classmethod
1723    def number(cls, number) -> Literal:
1724        return cls(this=str(number), is_string=False)
1725
1726    @classmethod
1727    def string(cls, string) -> Literal:
1728        return cls(this=str(string), is_string=True)
1729
1730    @property
1731    def output_name(self) -> str:
1732        return self.name
1733
1734
1735class Join(Expression):
1736    arg_types = {
1737        "this": True,
1738        "on": False,
1739        "side": False,
1740        "kind": False,
1741        "using": False,
1742        "method": False,
1743        "global": False,
1744        "hint": False,
1745    }
1746
1747    @property
1748    def method(self) -> str:
1749        return self.text("method").upper()
1750
1751    @property
1752    def kind(self) -> str:
1753        return self.text("kind").upper()
1754
1755    @property
1756    def side(self) -> str:
1757        return self.text("side").upper()
1758
1759    @property
1760    def hint(self) -> str:
1761        return self.text("hint").upper()
1762
1763    @property
1764    def alias_or_name(self) -> str:
1765        return self.this.alias_or_name
1766
1767    def on(
1768        self,
1769        *expressions: t.Optional[ExpOrStr],
1770        append: bool = True,
1771        dialect: DialectType = None,
1772        copy: bool = True,
1773        **opts,
1774    ) -> Join:
1775        """
1776        Append to or set the ON expressions.
1777
1778        Example:
1779            >>> import sqlglot
1780            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1781            'JOIN x ON y = 1'
1782
1783        Args:
1784            *expressions: the SQL code strings to parse.
1785                If an `Expression` instance is passed, it will be used as-is.
1786                Multiple expressions are combined with an AND operator.
1787            append: if `True`, AND the new expressions to any existing expression.
1788                Otherwise, this resets the expression.
1789            dialect: the dialect used to parse the input expressions.
1790            copy: if `False`, modify this expression instance in-place.
1791            opts: other options to use to parse the input expressions.
1792
1793        Returns:
1794            The modified Join expression.
1795        """
1796        join = _apply_conjunction_builder(
1797            *expressions,
1798            instance=self,
1799            arg="on",
1800            append=append,
1801            dialect=dialect,
1802            copy=copy,
1803            **opts,
1804        )
1805
1806        if join.kind == "CROSS":
1807            join.set("kind", None)
1808
1809        return join
1810
1811    def using(
1812        self,
1813        *expressions: t.Optional[ExpOrStr],
1814        append: bool = True,
1815        dialect: DialectType = None,
1816        copy: bool = True,
1817        **opts,
1818    ) -> Join:
1819        """
1820        Append to or set the USING expressions.
1821
1822        Example:
1823            >>> import sqlglot
1824            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1825            'JOIN x USING (foo, bla)'
1826
1827        Args:
1828            *expressions: the SQL code strings to parse.
1829                If an `Expression` instance is passed, it will be used as-is.
1830            append: if `True`, concatenate the new expressions to the existing "using" list.
1831                Otherwise, this resets the expression.
1832            dialect: the dialect used to parse the input expressions.
1833            copy: if `False`, modify this expression instance in-place.
1834            opts: other options to use to parse the input expressions.
1835
1836        Returns:
1837            The modified Join expression.
1838        """
1839        join = _apply_list_builder(
1840            *expressions,
1841            instance=self,
1842            arg="using",
1843            append=append,
1844            dialect=dialect,
1845            copy=copy,
1846            **opts,
1847        )
1848
1849        if join.kind == "CROSS":
1850            join.set("kind", None)
1851
1852        return join
1853
1854
1855class Lateral(UDTF):
1856    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1857
1858
1859class MatchRecognize(Expression):
1860    arg_types = {
1861        "partition_by": False,
1862        "order": False,
1863        "measures": False,
1864        "rows": False,
1865        "after": False,
1866        "pattern": False,
1867        "define": False,
1868        "alias": False,
1869    }
1870
1871
1872# Clickhouse FROM FINAL modifier
1873# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1874class Final(Expression):
1875    pass
1876
1877
1878class Offset(Expression):
1879    arg_types = {"this": False, "expression": True}
1880
1881
1882class Order(Expression):
1883    arg_types = {"this": False, "expressions": True}
1884
1885
1886# hive specific sorts
1887# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1888class Cluster(Order):
1889    pass
1890
1891
1892class Distribute(Order):
1893    pass
1894
1895
1896class Sort(Order):
1897    pass
1898
1899
1900class Ordered(Expression):
1901    arg_types = {"this": True, "desc": True, "nulls_first": True}
1902
1903
1904class Property(Expression):
1905    arg_types = {"this": True, "value": True}
1906
1907
1908class AlgorithmProperty(Property):
1909    arg_types = {"this": True}
1910
1911
1912class AutoIncrementProperty(Property):
1913    arg_types = {"this": True}
1914
1915
1916class BlockCompressionProperty(Property):
1917    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1918
1919
1920class CharacterSetProperty(Property):
1921    arg_types = {"this": True, "default": True}
1922
1923
1924class ChecksumProperty(Property):
1925    arg_types = {"on": False, "default": False}
1926
1927
1928class CollateProperty(Property):
1929    arg_types = {"this": True}
1930
1931
1932class CopyGrantsProperty(Property):
1933    arg_types = {}
1934
1935
1936class DataBlocksizeProperty(Property):
1937    arg_types = {
1938        "size": False,
1939        "units": False,
1940        "minimum": False,
1941        "maximum": False,
1942        "default": False,
1943    }
1944
1945
1946class DefinerProperty(Property):
1947    arg_types = {"this": True}
1948
1949
1950class DistKeyProperty(Property):
1951    arg_types = {"this": True}
1952
1953
1954class DistStyleProperty(Property):
1955    arg_types = {"this": True}
1956
1957
1958class EngineProperty(Property):
1959    arg_types = {"this": True}
1960
1961
1962class HeapProperty(Property):
1963    arg_types = {}
1964
1965
1966class ToTableProperty(Property):
1967    arg_types = {"this": True}
1968
1969
1970class ExecuteAsProperty(Property):
1971    arg_types = {"this": True}
1972
1973
1974class ExternalProperty(Property):
1975    arg_types = {"this": False}
1976
1977
1978class FallbackProperty(Property):
1979    arg_types = {"no": True, "protection": False}
1980
1981
1982class FileFormatProperty(Property):
1983    arg_types = {"this": True}
1984
1985
1986class FreespaceProperty(Property):
1987    arg_types = {"this": True, "percent": False}
1988
1989
1990class InputOutputFormat(Expression):
1991    arg_types = {"input_format": False, "output_format": False}
1992
1993
1994class IsolatedLoadingProperty(Property):
1995    arg_types = {
1996        "no": True,
1997        "concurrent": True,
1998        "for_all": True,
1999        "for_insert": True,
2000        "for_none": True,
2001    }
2002
2003
2004class JournalProperty(Property):
2005    arg_types = {
2006        "no": False,
2007        "dual": False,
2008        "before": False,
2009        "local": False,
2010        "after": False,
2011    }
2012
2013
2014class LanguageProperty(Property):
2015    arg_types = {"this": True}
2016
2017
2018# spark ddl
2019class ClusteredByProperty(Property):
2020    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2021
2022
2023class DictProperty(Property):
2024    arg_types = {"this": True, "kind": True, "settings": False}
2025
2026
2027class DictSubProperty(Property):
2028    pass
2029
2030
2031class DictRange(Property):
2032    arg_types = {"this": True, "min": True, "max": True}
2033
2034
2035# Clickhouse CREATE ... ON CLUSTER modifier
2036# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2037class OnCluster(Property):
2038    arg_types = {"this": True}
2039
2040
2041class LikeProperty(Property):
2042    arg_types = {"this": True, "expressions": False}
2043
2044
2045class LocationProperty(Property):
2046    arg_types = {"this": True}
2047
2048
2049class LockingProperty(Property):
2050    arg_types = {
2051        "this": False,
2052        "kind": True,
2053        "for_or_in": True,
2054        "lock_type": True,
2055        "override": False,
2056    }
2057
2058
2059class LogProperty(Property):
2060    arg_types = {"no": True}
2061
2062
2063class MaterializedProperty(Property):
2064    arg_types = {"this": False}
2065
2066
2067class MergeBlockRatioProperty(Property):
2068    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2069
2070
2071class NoPrimaryIndexProperty(Property):
2072    arg_types = {}
2073
2074
2075class OnProperty(Property):
2076    arg_types = {"this": True}
2077
2078
2079class OnCommitProperty(Property):
2080    arg_types = {"delete": False}
2081
2082
2083class PartitionedByProperty(Property):
2084    arg_types = {"this": True}
2085
2086
2087class ReturnsProperty(Property):
2088    arg_types = {"this": True, "is_table": False, "table": False}
2089
2090
2091class RowFormatProperty(Property):
2092    arg_types = {"this": True}
2093
2094
2095class RowFormatDelimitedProperty(Property):
2096    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2097    arg_types = {
2098        "fields": False,
2099        "escaped": False,
2100        "collection_items": False,
2101        "map_keys": False,
2102        "lines": False,
2103        "null": False,
2104        "serde": False,
2105    }
2106
2107
2108class RowFormatSerdeProperty(Property):
2109    arg_types = {"this": True, "serde_properties": False}
2110
2111
2112# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2113class QueryTransform(Expression):
2114    arg_types = {
2115        "expressions": True,
2116        "command_script": True,
2117        "schema": False,
2118        "row_format_before": False,
2119        "record_writer": False,
2120        "row_format_after": False,
2121        "record_reader": False,
2122    }
2123
2124
2125class SchemaCommentProperty(Property):
2126    arg_types = {"this": True}
2127
2128
2129class SerdeProperties(Property):
2130    arg_types = {"expressions": True}
2131
2132
2133class SetProperty(Property):
2134    arg_types = {"multi": True}
2135
2136
2137class SettingsProperty(Property):
2138    arg_types = {"expressions": True}
2139
2140
2141class SortKeyProperty(Property):
2142    arg_types = {"this": True, "compound": False}
2143
2144
2145class SqlSecurityProperty(Property):
2146    arg_types = {"definer": True}
2147
2148
2149class StabilityProperty(Property):
2150    arg_types = {"this": True}
2151
2152
2153class TemporaryProperty(Property):
2154    arg_types = {}
2155
2156
2157class TransientProperty(Property):
2158    arg_types = {"this": False}
2159
2160
2161class VolatileProperty(Property):
2162    arg_types = {"this": False}
2163
2164
2165class WithDataProperty(Property):
2166    arg_types = {"no": True, "statistics": False}
2167
2168
2169class WithJournalTableProperty(Property):
2170    arg_types = {"this": True}
2171
2172
2173class Properties(Expression):
2174    arg_types = {"expressions": True}
2175
2176    NAME_TO_PROPERTY = {
2177        "ALGORITHM": AlgorithmProperty,
2178        "AUTO_INCREMENT": AutoIncrementProperty,
2179        "CHARACTER SET": CharacterSetProperty,
2180        "CLUSTERED_BY": ClusteredByProperty,
2181        "COLLATE": CollateProperty,
2182        "COMMENT": SchemaCommentProperty,
2183        "DEFINER": DefinerProperty,
2184        "DISTKEY": DistKeyProperty,
2185        "DISTSTYLE": DistStyleProperty,
2186        "ENGINE": EngineProperty,
2187        "EXECUTE AS": ExecuteAsProperty,
2188        "FORMAT": FileFormatProperty,
2189        "LANGUAGE": LanguageProperty,
2190        "LOCATION": LocationProperty,
2191        "PARTITIONED_BY": PartitionedByProperty,
2192        "RETURNS": ReturnsProperty,
2193        "ROW_FORMAT": RowFormatProperty,
2194        "SORTKEY": SortKeyProperty,
2195    }
2196
2197    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2198
2199    # CREATE property locations
2200    # Form: schema specified
2201    #   create [POST_CREATE]
2202    #     table a [POST_NAME]
2203    #     (b int) [POST_SCHEMA]
2204    #     with ([POST_WITH])
2205    #     index (b) [POST_INDEX]
2206    #
2207    # Form: alias selection
2208    #   create [POST_CREATE]
2209    #     table a [POST_NAME]
2210    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2211    #     index (c) [POST_INDEX]
2212    class Location(AutoName):
2213        POST_CREATE = auto()
2214        POST_NAME = auto()
2215        POST_SCHEMA = auto()
2216        POST_WITH = auto()
2217        POST_ALIAS = auto()
2218        POST_EXPRESSION = auto()
2219        POST_INDEX = auto()
2220        UNSUPPORTED = auto()
2221
2222    @classmethod
2223    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2224        expressions = []
2225        for key, value in properties_dict.items():
2226            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2227            if property_cls:
2228                expressions.append(property_cls(this=convert(value)))
2229            else:
2230                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2231
2232        return cls(expressions=expressions)
2233
2234
2235class Qualify(Expression):
2236    pass
2237
2238
2239# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2240class Return(Expression):
2241    pass
2242
2243
2244class Reference(Expression):
2245    arg_types = {"this": True, "expressions": False, "options": False}
2246
2247
2248class Tuple(Expression):
2249    arg_types = {"expressions": False}
2250
2251    def isin(
2252        self,
2253        *expressions: t.Any,
2254        query: t.Optional[ExpOrStr] = None,
2255        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2256        copy: bool = True,
2257        **opts,
2258    ) -> In:
2259        return In(
2260            this=maybe_copy(self, copy),
2261            expressions=[convert(e, copy=copy) for e in expressions],
2262            query=maybe_parse(query, copy=copy, **opts) if query else None,
2263            unnest=Unnest(
2264                expressions=[
2265                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2266                ]
2267            )
2268            if unnest
2269            else None,
2270        )
2271
2272
2273class Subqueryable(Unionable):
2274    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2275        """
2276        Convert this expression to an aliased expression that can be used as a Subquery.
2277
2278        Example:
2279            >>> subquery = Select().select("x").from_("tbl").subquery()
2280            >>> Select().select("x").from_(subquery).sql()
2281            'SELECT x FROM (SELECT x FROM tbl)'
2282
2283        Args:
2284            alias (str | Identifier): an optional alias for the subquery
2285            copy (bool): if `False`, modify this expression instance in-place.
2286
2287        Returns:
2288            Alias: the subquery
2289        """
2290        instance = maybe_copy(self, copy)
2291        if not isinstance(alias, Expression):
2292            alias = TableAlias(this=to_identifier(alias)) if alias else None
2293
2294        return Subquery(this=instance, alias=alias)
2295
2296    def limit(
2297        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2298    ) -> Select:
2299        raise NotImplementedError
2300
2301    @property
2302    def ctes(self):
2303        with_ = self.args.get("with")
2304        if not with_:
2305            return []
2306        return with_.expressions
2307
2308    @property
2309    def selects(self) -> t.List[Expression]:
2310        raise NotImplementedError("Subqueryable objects must implement `selects`")
2311
2312    @property
2313    def named_selects(self) -> t.List[str]:
2314        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2315
2316    def select(
2317        self,
2318        *expressions: t.Optional[ExpOrStr],
2319        append: bool = True,
2320        dialect: DialectType = None,
2321        copy: bool = True,
2322        **opts,
2323    ) -> Subqueryable:
2324        raise NotImplementedError("Subqueryable objects must implement `select`")
2325
2326    def with_(
2327        self,
2328        alias: ExpOrStr,
2329        as_: ExpOrStr,
2330        recursive: t.Optional[bool] = None,
2331        append: bool = True,
2332        dialect: DialectType = None,
2333        copy: bool = True,
2334        **opts,
2335    ) -> Subqueryable:
2336        """
2337        Append to or set the common table expressions.
2338
2339        Example:
2340            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2341            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2342
2343        Args:
2344            alias: the SQL code string to parse as the table name.
2345                If an `Expression` instance is passed, this is used as-is.
2346            as_: the SQL code string to parse as the table expression.
2347                If an `Expression` instance is passed, it will be used as-is.
2348            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2349            append: if `True`, add to any existing expressions.
2350                Otherwise, this resets the expressions.
2351            dialect: the dialect used to parse the input expression.
2352            copy: if `False`, modify this expression instance in-place.
2353            opts: other options to use to parse the input expressions.
2354
2355        Returns:
2356            The modified expression.
2357        """
2358        return _apply_cte_builder(
2359            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2360        )
2361
2362
2363QUERY_MODIFIERS = {
2364    "match": False,
2365    "laterals": False,
2366    "joins": False,
2367    "connect": False,
2368    "pivots": False,
2369    "where": False,
2370    "group": False,
2371    "having": False,
2372    "qualify": False,
2373    "windows": False,
2374    "distribute": False,
2375    "sort": False,
2376    "cluster": False,
2377    "order": False,
2378    "limit": False,
2379    "offset": False,
2380    "locks": False,
2381    "sample": False,
2382    "settings": False,
2383    "format": False,
2384}
2385
2386
2387# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2388class WithTableHint(Expression):
2389    arg_types = {"expressions": True}
2390
2391
2392# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2393class IndexTableHint(Expression):
2394    arg_types = {"this": True, "expressions": False, "target": False}
2395
2396
2397class Table(Expression):
2398    arg_types = {
2399        "this": True,
2400        "alias": False,
2401        "db": False,
2402        "catalog": False,
2403        "laterals": False,
2404        "joins": False,
2405        "pivots": False,
2406        "hints": False,
2407        "system_time": False,
2408    }
2409
2410    @property
2411    def name(self) -> str:
2412        if isinstance(self.this, Func):
2413            return ""
2414        return self.this.name
2415
2416    @property
2417    def db(self) -> str:
2418        return self.text("db")
2419
2420    @property
2421    def catalog(self) -> str:
2422        return self.text("catalog")
2423
2424    @property
2425    def selects(self) -> t.List[Expression]:
2426        return []
2427
2428    @property
2429    def named_selects(self) -> t.List[str]:
2430        return []
2431
2432    @property
2433    def parts(self) -> t.List[Identifier]:
2434        """Return the parts of a table in order catalog, db, table."""
2435        parts: t.List[Identifier] = []
2436
2437        for arg in ("catalog", "db", "this"):
2438            part = self.args.get(arg)
2439
2440            if isinstance(part, Identifier):
2441                parts.append(part)
2442            elif isinstance(part, Dot):
2443                parts.extend(part.flatten())
2444
2445        return parts
2446
2447
2448# See the TSQL "Querying data in a system-versioned temporal table" page
2449class SystemTime(Expression):
2450    arg_types = {
2451        "this": False,
2452        "expression": False,
2453        "kind": True,
2454    }
2455
2456
2457class Union(Subqueryable):
2458    arg_types = {
2459        "with": False,
2460        "this": True,
2461        "expression": True,
2462        "distinct": False,
2463        **QUERY_MODIFIERS,
2464    }
2465
2466    def limit(
2467        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2468    ) -> Select:
2469        """
2470        Set the LIMIT expression.
2471
2472        Example:
2473            >>> select("1").union(select("1")).limit(1).sql()
2474            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2475
2476        Args:
2477            expression: the SQL code string to parse.
2478                This can also be an integer.
2479                If a `Limit` instance is passed, this is used as-is.
2480                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2481            dialect: the dialect used to parse the input expression.
2482            copy: if `False`, modify this expression instance in-place.
2483            opts: other options to use to parse the input expressions.
2484
2485        Returns:
2486            The limited subqueryable.
2487        """
2488        return (
2489            select("*")
2490            .from_(self.subquery(alias="_l_0", copy=copy))
2491            .limit(expression, dialect=dialect, copy=False, **opts)
2492        )
2493
2494    def select(
2495        self,
2496        *expressions: t.Optional[ExpOrStr],
2497        append: bool = True,
2498        dialect: DialectType = None,
2499        copy: bool = True,
2500        **opts,
2501    ) -> Union:
2502        """Append to or set the SELECT of the union recursively.
2503
2504        Example:
2505            >>> from sqlglot import parse_one
2506            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2507            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2508
2509        Args:
2510            *expressions: the SQL code strings to parse.
2511                If an `Expression` instance is passed, it will be used as-is.
2512            append: if `True`, add to any existing expressions.
2513                Otherwise, this resets the expressions.
2514            dialect: the dialect used to parse the input expressions.
2515            copy: if `False`, modify this expression instance in-place.
2516            opts: other options to use to parse the input expressions.
2517
2518        Returns:
2519            Union: the modified expression.
2520        """
2521        this = self.copy() if copy else self
2522        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2523        this.expression.unnest().select(
2524            *expressions, append=append, dialect=dialect, copy=False, **opts
2525        )
2526        return this
2527
2528    @property
2529    def named_selects(self) -> t.List[str]:
2530        return self.this.unnest().named_selects
2531
2532    @property
2533    def is_star(self) -> bool:
2534        return self.this.is_star or self.expression.is_star
2535
2536    @property
2537    def selects(self) -> t.List[Expression]:
2538        return self.this.unnest().selects
2539
2540    @property
2541    def left(self):
2542        return self.this
2543
2544    @property
2545    def right(self):
2546        return self.expression
2547
2548
2549class Except(Union):
2550    pass
2551
2552
2553class Intersect(Union):
2554    pass
2555
2556
2557class Unnest(UDTF):
2558    arg_types = {
2559        "expressions": True,
2560        "ordinality": False,
2561        "alias": False,
2562        "offset": False,
2563    }
2564
2565
2566class Update(Expression):
2567    arg_types = {
2568        "with": False,
2569        "this": False,
2570        "expressions": True,
2571        "from": False,
2572        "where": False,
2573        "returning": False,
2574        "limit": False,
2575    }
2576
2577
2578class Values(UDTF):
2579    arg_types = {
2580        "expressions": True,
2581        "ordinality": False,
2582        "alias": False,
2583    }
2584
2585
2586class Var(Expression):
2587    pass
2588
2589
2590class Schema(Expression):
2591    arg_types = {"this": False, "expressions": False}
2592
2593
2594# https://dev.mysql.com/doc/refman/8.0/en/select.html
2595# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2596class Lock(Expression):
2597    arg_types = {"update": True, "expressions": False, "wait": False}
2598
2599
2600class Select(Subqueryable):
2601    arg_types = {
2602        "with": False,
2603        "kind": False,
2604        "expressions": False,
2605        "hint": False,
2606        "distinct": False,
2607        "into": False,
2608        "from": False,
2609        **QUERY_MODIFIERS,
2610    }
2611
2612    def from_(
2613        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2614    ) -> Select:
2615        """
2616        Set the FROM expression.
2617
2618        Example:
2619            >>> Select().from_("tbl").select("x").sql()
2620            'SELECT x FROM tbl'
2621
2622        Args:
2623            expression : the SQL code strings to parse.
2624                If a `From` instance is passed, this is used as-is.
2625                If another `Expression` instance is passed, it will be wrapped in a `From`.
2626            dialect: the dialect used to parse the input expression.
2627            copy: if `False`, modify this expression instance in-place.
2628            opts: other options to use to parse the input expressions.
2629
2630        Returns:
2631            The modified Select expression.
2632        """
2633        return _apply_builder(
2634            expression=expression,
2635            instance=self,
2636            arg="from",
2637            into=From,
2638            prefix="FROM",
2639            dialect=dialect,
2640            copy=copy,
2641            **opts,
2642        )
2643
2644    def group_by(
2645        self,
2646        *expressions: t.Optional[ExpOrStr],
2647        append: bool = True,
2648        dialect: DialectType = None,
2649        copy: bool = True,
2650        **opts,
2651    ) -> Select:
2652        """
2653        Set the GROUP BY expression.
2654
2655        Example:
2656            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2657            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2658
2659        Args:
2660            *expressions: the SQL code strings to parse.
2661                If a `Group` instance is passed, this is used as-is.
2662                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2663                If nothing is passed in then a group by is not applied to the expression
2664            append: if `True`, add to any existing expressions.
2665                Otherwise, this flattens all the `Group` expression into a single expression.
2666            dialect: the dialect used to parse the input expression.
2667            copy: if `False`, modify this expression instance in-place.
2668            opts: other options to use to parse the input expressions.
2669
2670        Returns:
2671            The modified Select expression.
2672        """
2673        if not expressions:
2674            return self if not copy else self.copy()
2675
2676        return _apply_child_list_builder(
2677            *expressions,
2678            instance=self,
2679            arg="group",
2680            append=append,
2681            copy=copy,
2682            prefix="GROUP BY",
2683            into=Group,
2684            dialect=dialect,
2685            **opts,
2686        )
2687
2688    def order_by(
2689        self,
2690        *expressions: t.Optional[ExpOrStr],
2691        append: bool = True,
2692        dialect: DialectType = None,
2693        copy: bool = True,
2694        **opts,
2695    ) -> Select:
2696        """
2697        Set the ORDER BY expression.
2698
2699        Example:
2700            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2701            'SELECT x FROM tbl ORDER BY x DESC'
2702
2703        Args:
2704            *expressions: the SQL code strings to parse.
2705                If a `Group` instance is passed, this is used as-is.
2706                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2707            append: if `True`, add to any existing expressions.
2708                Otherwise, this flattens all the `Order` expression into a single expression.
2709            dialect: the dialect used to parse the input expression.
2710            copy: if `False`, modify this expression instance in-place.
2711            opts: other options to use to parse the input expressions.
2712
2713        Returns:
2714            The modified Select expression.
2715        """
2716        return _apply_child_list_builder(
2717            *expressions,
2718            instance=self,
2719            arg="order",
2720            append=append,
2721            copy=copy,
2722            prefix="ORDER BY",
2723            into=Order,
2724            dialect=dialect,
2725            **opts,
2726        )
2727
2728    def sort_by(
2729        self,
2730        *expressions: t.Optional[ExpOrStr],
2731        append: bool = True,
2732        dialect: DialectType = None,
2733        copy: bool = True,
2734        **opts,
2735    ) -> Select:
2736        """
2737        Set the SORT BY expression.
2738
2739        Example:
2740            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2741            'SELECT x FROM tbl SORT BY x DESC'
2742
2743        Args:
2744            *expressions: the SQL code strings to parse.
2745                If a `Group` instance is passed, this is used as-is.
2746                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2747            append: if `True`, add to any existing expressions.
2748                Otherwise, this flattens all the `Order` expression into a single expression.
2749            dialect: the dialect used to parse the input expression.
2750            copy: if `False`, modify this expression instance in-place.
2751            opts: other options to use to parse the input expressions.
2752
2753        Returns:
2754            The modified Select expression.
2755        """
2756        return _apply_child_list_builder(
2757            *expressions,
2758            instance=self,
2759            arg="sort",
2760            append=append,
2761            copy=copy,
2762            prefix="SORT BY",
2763            into=Sort,
2764            dialect=dialect,
2765            **opts,
2766        )
2767
2768    def cluster_by(
2769        self,
2770        *expressions: t.Optional[ExpOrStr],
2771        append: bool = True,
2772        dialect: DialectType = None,
2773        copy: bool = True,
2774        **opts,
2775    ) -> Select:
2776        """
2777        Set the CLUSTER BY expression.
2778
2779        Example:
2780            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2781            'SELECT x FROM tbl CLUSTER BY x DESC'
2782
2783        Args:
2784            *expressions: the SQL code strings to parse.
2785                If a `Group` instance is passed, this is used as-is.
2786                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2787            append: if `True`, add to any existing expressions.
2788                Otherwise, this flattens all the `Order` expression into a single expression.
2789            dialect: the dialect used to parse the input expression.
2790            copy: if `False`, modify this expression instance in-place.
2791            opts: other options to use to parse the input expressions.
2792
2793        Returns:
2794            The modified Select expression.
2795        """
2796        return _apply_child_list_builder(
2797            *expressions,
2798            instance=self,
2799            arg="cluster",
2800            append=append,
2801            copy=copy,
2802            prefix="CLUSTER BY",
2803            into=Cluster,
2804            dialect=dialect,
2805            **opts,
2806        )
2807
2808    def limit(
2809        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2810    ) -> Select:
2811        """
2812        Set the LIMIT expression.
2813
2814        Example:
2815            >>> Select().from_("tbl").select("x").limit(10).sql()
2816            'SELECT x FROM tbl LIMIT 10'
2817
2818        Args:
2819            expression: the SQL code string to parse.
2820                This can also be an integer.
2821                If a `Limit` instance is passed, this is used as-is.
2822                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2823            dialect: the dialect used to parse the input expression.
2824            copy: if `False`, modify this expression instance in-place.
2825            opts: other options to use to parse the input expressions.
2826
2827        Returns:
2828            Select: the modified expression.
2829        """
2830        return _apply_builder(
2831            expression=expression,
2832            instance=self,
2833            arg="limit",
2834            into=Limit,
2835            prefix="LIMIT",
2836            dialect=dialect,
2837            copy=copy,
2838            **opts,
2839        )
2840
2841    def offset(
2842        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2843    ) -> Select:
2844        """
2845        Set the OFFSET expression.
2846
2847        Example:
2848            >>> Select().from_("tbl").select("x").offset(10).sql()
2849            'SELECT x FROM tbl OFFSET 10'
2850
2851        Args:
2852            expression: the SQL code string to parse.
2853                This can also be an integer.
2854                If a `Offset` instance is passed, this is used as-is.
2855                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2856            dialect: the dialect used to parse the input expression.
2857            copy: if `False`, modify this expression instance in-place.
2858            opts: other options to use to parse the input expressions.
2859
2860        Returns:
2861            The modified Select expression.
2862        """
2863        return _apply_builder(
2864            expression=expression,
2865            instance=self,
2866            arg="offset",
2867            into=Offset,
2868            prefix="OFFSET",
2869            dialect=dialect,
2870            copy=copy,
2871            **opts,
2872        )
2873
2874    def select(
2875        self,
2876        *expressions: t.Optional[ExpOrStr],
2877        append: bool = True,
2878        dialect: DialectType = None,
2879        copy: bool = True,
2880        **opts,
2881    ) -> Select:
2882        """
2883        Append to or set the SELECT expressions.
2884
2885        Example:
2886            >>> Select().select("x", "y").sql()
2887            'SELECT x, y'
2888
2889        Args:
2890            *expressions: the SQL code strings to parse.
2891                If an `Expression` instance is passed, it will be used as-is.
2892            append: if `True`, add to any existing expressions.
2893                Otherwise, this resets the expressions.
2894            dialect: the dialect used to parse the input expressions.
2895            copy: if `False`, modify this expression instance in-place.
2896            opts: other options to use to parse the input expressions.
2897
2898        Returns:
2899            The modified Select expression.
2900        """
2901        return _apply_list_builder(
2902            *expressions,
2903            instance=self,
2904            arg="expressions",
2905            append=append,
2906            dialect=dialect,
2907            copy=copy,
2908            **opts,
2909        )
2910
2911    def lateral(
2912        self,
2913        *expressions: t.Optional[ExpOrStr],
2914        append: bool = True,
2915        dialect: DialectType = None,
2916        copy: bool = True,
2917        **opts,
2918    ) -> Select:
2919        """
2920        Append to or set the LATERAL expressions.
2921
2922        Example:
2923            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2924            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2925
2926        Args:
2927            *expressions: the SQL code strings to parse.
2928                If an `Expression` instance is passed, it will be used as-is.
2929            append: if `True`, add to any existing expressions.
2930                Otherwise, this resets the expressions.
2931            dialect: the dialect used to parse the input expressions.
2932            copy: if `False`, modify this expression instance in-place.
2933            opts: other options to use to parse the input expressions.
2934
2935        Returns:
2936            The modified Select expression.
2937        """
2938        return _apply_list_builder(
2939            *expressions,
2940            instance=self,
2941            arg="laterals",
2942            append=append,
2943            into=Lateral,
2944            prefix="LATERAL VIEW",
2945            dialect=dialect,
2946            copy=copy,
2947            **opts,
2948        )
2949
2950    def join(
2951        self,
2952        expression: ExpOrStr,
2953        on: t.Optional[ExpOrStr] = None,
2954        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
2955        append: bool = True,
2956        join_type: t.Optional[str] = None,
2957        join_alias: t.Optional[Identifier | str] = None,
2958        dialect: DialectType = None,
2959        copy: bool = True,
2960        **opts,
2961    ) -> Select:
2962        """
2963        Append to or set the JOIN expressions.
2964
2965        Example:
2966            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2967            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2968
2969            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2970            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2971
2972            Use `join_type` to change the type of join:
2973
2974            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2975            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2976
2977        Args:
2978            expression: the SQL code string to parse.
2979                If an `Expression` instance is passed, it will be used as-is.
2980            on: optionally specify the join "on" criteria as a SQL string.
2981                If an `Expression` instance is passed, it will be used as-is.
2982            using: optionally specify the join "using" criteria as a SQL string.
2983                If an `Expression` instance is passed, it will be used as-is.
2984            append: if `True`, add to any existing expressions.
2985                Otherwise, this resets the expressions.
2986            join_type: if set, alter the parsed join type.
2987            join_alias: an optional alias for the joined source.
2988            dialect: the dialect used to parse the input expressions.
2989            copy: if `False`, modify this expression instance in-place.
2990            opts: other options to use to parse the input expressions.
2991
2992        Returns:
2993            Select: the modified expression.
2994        """
2995        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
2996
2997        try:
2998            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2999        except ParseError:
3000            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3001
3002        join = expression if isinstance(expression, Join) else Join(this=expression)
3003
3004        if isinstance(join.this, Select):
3005            join.this.replace(join.this.subquery())
3006
3007        if join_type:
3008            method: t.Optional[Token]
3009            side: t.Optional[Token]
3010            kind: t.Optional[Token]
3011
3012            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3013
3014            if method:
3015                join.set("method", method.text)
3016            if side:
3017                join.set("side", side.text)
3018            if kind:
3019                join.set("kind", kind.text)
3020
3021        if on:
3022            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3023            join.set("on", on)
3024
3025        if using:
3026            join = _apply_list_builder(
3027                *ensure_list(using),
3028                instance=join,
3029                arg="using",
3030                append=append,
3031                copy=copy,
3032                into=Identifier,
3033                **opts,
3034            )
3035
3036        if join_alias:
3037            join.set("this", alias_(join.this, join_alias, table=True))
3038
3039        return _apply_list_builder(
3040            join,
3041            instance=self,
3042            arg="joins",
3043            append=append,
3044            copy=copy,
3045            **opts,
3046        )
3047
3048    def where(
3049        self,
3050        *expressions: t.Optional[ExpOrStr],
3051        append: bool = True,
3052        dialect: DialectType = None,
3053        copy: bool = True,
3054        **opts,
3055    ) -> Select:
3056        """
3057        Append to or set the WHERE expressions.
3058
3059        Example:
3060            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3061            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3062
3063        Args:
3064            *expressions: the SQL code strings to parse.
3065                If an `Expression` instance is passed, it will be used as-is.
3066                Multiple expressions are combined with an AND operator.
3067            append: if `True`, AND the new expressions to any existing expression.
3068                Otherwise, this resets the expression.
3069            dialect: the dialect used to parse the input expressions.
3070            copy: if `False`, modify this expression instance in-place.
3071            opts: other options to use to parse the input expressions.
3072
3073        Returns:
3074            Select: the modified expression.
3075        """
3076        return _apply_conjunction_builder(
3077            *expressions,
3078            instance=self,
3079            arg="where",
3080            append=append,
3081            into=Where,
3082            dialect=dialect,
3083            copy=copy,
3084            **opts,
3085        )
3086
3087    def having(
3088        self,
3089        *expressions: t.Optional[ExpOrStr],
3090        append: bool = True,
3091        dialect: DialectType = None,
3092        copy: bool = True,
3093        **opts,
3094    ) -> Select:
3095        """
3096        Append to or set the HAVING expressions.
3097
3098        Example:
3099            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3100            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3101
3102        Args:
3103            *expressions: the SQL code strings to parse.
3104                If an `Expression` instance is passed, it will be used as-is.
3105                Multiple expressions are combined with an AND operator.
3106            append: if `True`, AND the new expressions to any existing expression.
3107                Otherwise, this resets the expression.
3108            dialect: the dialect used to parse the input expressions.
3109            copy: if `False`, modify this expression instance in-place.
3110            opts: other options to use to parse the input expressions.
3111
3112        Returns:
3113            The modified Select expression.
3114        """
3115        return _apply_conjunction_builder(
3116            *expressions,
3117            instance=self,
3118            arg="having",
3119            append=append,
3120            into=Having,
3121            dialect=dialect,
3122            copy=copy,
3123            **opts,
3124        )
3125
3126    def window(
3127        self,
3128        *expressions: t.Optional[ExpOrStr],
3129        append: bool = True,
3130        dialect: DialectType = None,
3131        copy: bool = True,
3132        **opts,
3133    ) -> Select:
3134        return _apply_list_builder(
3135            *expressions,
3136            instance=self,
3137            arg="windows",
3138            append=append,
3139            into=Window,
3140            dialect=dialect,
3141            copy=copy,
3142            **opts,
3143        )
3144
3145    def qualify(
3146        self,
3147        *expressions: t.Optional[ExpOrStr],
3148        append: bool = True,
3149        dialect: DialectType = None,
3150        copy: bool = True,
3151        **opts,
3152    ) -> Select:
3153        return _apply_conjunction_builder(
3154            *expressions,
3155            instance=self,
3156            arg="qualify",
3157            append=append,
3158            into=Qualify,
3159            dialect=dialect,
3160            copy=copy,
3161            **opts,
3162        )
3163
3164    def distinct(
3165        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3166    ) -> Select:
3167        """
3168        Set the OFFSET expression.
3169
3170        Example:
3171            >>> Select().from_("tbl").select("x").distinct().sql()
3172            'SELECT DISTINCT x FROM tbl'
3173
3174        Args:
3175            ons: the expressions to distinct on
3176            distinct: whether the Select should be distinct
3177            copy: if `False`, modify this expression instance in-place.
3178
3179        Returns:
3180            Select: the modified expression.
3181        """
3182        instance = maybe_copy(self, copy)
3183        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3184        instance.set("distinct", Distinct(on=on) if distinct else None)
3185        return instance
3186
3187    def ctas(
3188        self,
3189        table: ExpOrStr,
3190        properties: t.Optional[t.Dict] = None,
3191        dialect: DialectType = None,
3192        copy: bool = True,
3193        **opts,
3194    ) -> Create:
3195        """
3196        Convert this expression to a CREATE TABLE AS statement.
3197
3198        Example:
3199            >>> Select().select("*").from_("tbl").ctas("x").sql()
3200            'CREATE TABLE x AS SELECT * FROM tbl'
3201
3202        Args:
3203            table: the SQL code string to parse as the table name.
3204                If another `Expression` instance is passed, it will be used as-is.
3205            properties: an optional mapping of table properties
3206            dialect: the dialect used to parse the input table.
3207            copy: if `False`, modify this expression instance in-place.
3208            opts: other options to use to parse the input table.
3209
3210        Returns:
3211            The new Create expression.
3212        """
3213        instance = maybe_copy(self, copy)
3214        table_expression = maybe_parse(
3215            table,
3216            into=Table,
3217            dialect=dialect,
3218            **opts,
3219        )
3220        properties_expression = None
3221        if properties:
3222            properties_expression = Properties.from_dict(properties)
3223
3224        return Create(
3225            this=table_expression,
3226            kind="table",
3227            expression=instance,
3228            properties=properties_expression,
3229        )
3230
3231    def lock(self, update: bool = True, copy: bool = True) -> Select:
3232        """
3233        Set the locking read mode for this expression.
3234
3235        Examples:
3236            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3237            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3238
3239            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3240            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3241
3242        Args:
3243            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3244            copy: if `False`, modify this expression instance in-place.
3245
3246        Returns:
3247            The modified expression.
3248        """
3249        inst = maybe_copy(self, copy)
3250        inst.set("locks", [Lock(update=update)])
3251
3252        return inst
3253
3254    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3255        """
3256        Set hints for this expression.
3257
3258        Examples:
3259            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3260            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3261
3262        Args:
3263            hints: The SQL code strings to parse as the hints.
3264                If an `Expression` instance is passed, it will be used as-is.
3265            dialect: The dialect used to parse the hints.
3266            copy: If `False`, modify this expression instance in-place.
3267
3268        Returns:
3269            The modified expression.
3270        """
3271        inst = maybe_copy(self, copy)
3272        inst.set(
3273            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3274        )
3275
3276        return inst
3277
3278    @property
3279    def named_selects(self) -> t.List[str]:
3280        return [e.output_name for e in self.expressions if e.alias_or_name]
3281
3282    @property
3283    def is_star(self) -> bool:
3284        return any(expression.is_star for expression in self.expressions)
3285
3286    @property
3287    def selects(self) -> t.List[Expression]:
3288        return self.expressions
3289
3290
3291class Subquery(DerivedTable, Unionable):
3292    arg_types = {
3293        "this": True,
3294        "alias": False,
3295        "with": False,
3296        **QUERY_MODIFIERS,
3297    }
3298
3299    def unnest(self):
3300        """
3301        Returns the first non subquery.
3302        """
3303        expression = self
3304        while isinstance(expression, Subquery):
3305            expression = expression.this
3306        return expression
3307
3308    def unwrap(self) -> Subquery:
3309        expression = self
3310        while expression.same_parent and expression.is_wrapper:
3311            expression = t.cast(Subquery, expression.parent)
3312        return expression
3313
3314    @property
3315    def is_wrapper(self) -> bool:
3316        """
3317        Whether this Subquery acts as a simple wrapper around another expression.
3318
3319        SELECT * FROM (((SELECT * FROM t)))
3320                      ^
3321                      This corresponds to a "wrapper" Subquery node
3322        """
3323        return all(v is None for k, v in self.args.items() if k != "this")
3324
3325    @property
3326    def is_star(self) -> bool:
3327        return self.this.is_star
3328
3329    @property
3330    def output_name(self) -> str:
3331        return self.alias
3332
3333
3334class TableSample(Expression):
3335    arg_types = {
3336        "this": False,
3337        "method": False,
3338        "bucket_numerator": False,
3339        "bucket_denominator": False,
3340        "bucket_field": False,
3341        "percent": False,
3342        "rows": False,
3343        "size": False,
3344        "seed": False,
3345        "kind": False,
3346    }
3347
3348
3349class Tag(Expression):
3350    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3351
3352    arg_types = {
3353        "this": False,
3354        "prefix": False,
3355        "postfix": False,
3356    }
3357
3358
3359# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3360# https://duckdb.org/docs/sql/statements/pivot
3361class Pivot(Expression):
3362    arg_types = {
3363        "this": False,
3364        "alias": False,
3365        "expressions": True,
3366        "field": False,
3367        "unpivot": False,
3368        "using": False,
3369        "group": False,
3370        "columns": False,
3371        "include_nulls": False,
3372    }
3373
3374
3375class Window(Condition):
3376    arg_types = {
3377        "this": True,
3378        "partition_by": False,
3379        "order": False,
3380        "spec": False,
3381        "alias": False,
3382        "over": False,
3383        "first": False,
3384    }
3385
3386
3387class WindowSpec(Expression):
3388    arg_types = {
3389        "kind": False,
3390        "start": False,
3391        "start_side": False,
3392        "end": False,
3393        "end_side": False,
3394    }
3395
3396
3397class Where(Expression):
3398    pass
3399
3400
3401class Star(Expression):
3402    arg_types = {"except": False, "replace": False}
3403
3404    @property
3405    def name(self) -> str:
3406        return "*"
3407
3408    @property
3409    def output_name(self) -> str:
3410        return self.name
3411
3412
3413class Parameter(Condition):
3414    arg_types = {"this": True, "wrapped": False}
3415
3416
3417class SessionParameter(Condition):
3418    arg_types = {"this": True, "kind": False}
3419
3420
3421class Placeholder(Condition):
3422    arg_types = {"this": False, "kind": False}
3423
3424
3425class Null(Condition):
3426    arg_types: t.Dict[str, t.Any] = {}
3427
3428    @property
3429    def name(self) -> str:
3430        return "NULL"
3431
3432
3433class Boolean(Condition):
3434    pass
3435
3436
3437class DataTypeParam(Expression):
3438    arg_types = {"this": True, "expression": False}
3439
3440
3441class DataType(Expression):
3442    arg_types = {
3443        "this": True,
3444        "expressions": False,
3445        "nested": False,
3446        "values": False,
3447        "prefix": False,
3448        "kind": False,
3449    }
3450
3451    class Type(AutoName):
3452        ARRAY = auto()
3453        BIGDECIMAL = auto()
3454        BIGINT = auto()
3455        BIGSERIAL = auto()
3456        BINARY = auto()
3457        BIT = auto()
3458        BOOLEAN = auto()
3459        CHAR = auto()
3460        DATE = auto()
3461        DATEMULTIRANGE = auto()
3462        DATERANGE = auto()
3463        DATETIME = auto()
3464        DATETIME64 = auto()
3465        DECIMAL = auto()
3466        DOUBLE = auto()
3467        ENUM = auto()
3468        ENUM8 = auto()
3469        ENUM16 = auto()
3470        FIXEDSTRING = auto()
3471        FLOAT = auto()
3472        GEOGRAPHY = auto()
3473        GEOMETRY = auto()
3474        HLLSKETCH = auto()
3475        HSTORE = auto()
3476        IMAGE = auto()
3477        INET = auto()
3478        INT = auto()
3479        INT128 = auto()
3480        INT256 = auto()
3481        INT4MULTIRANGE = auto()
3482        INT4RANGE = auto()
3483        INT8MULTIRANGE = auto()
3484        INT8RANGE = auto()
3485        INTERVAL = auto()
3486        IPADDRESS = auto()
3487        IPPREFIX = auto()
3488        JSON = auto()
3489        JSONB = auto()
3490        LONGBLOB = auto()
3491        LONGTEXT = auto()
3492        LOWCARDINALITY = auto()
3493        MAP = auto()
3494        MEDIUMBLOB = auto()
3495        MEDIUMINT = auto()
3496        MEDIUMTEXT = auto()
3497        MONEY = auto()
3498        NCHAR = auto()
3499        NESTED = auto()
3500        NULL = auto()
3501        NULLABLE = auto()
3502        NUMMULTIRANGE = auto()
3503        NUMRANGE = auto()
3504        NVARCHAR = auto()
3505        OBJECT = auto()
3506        ROWVERSION = auto()
3507        SERIAL = auto()
3508        SET = auto()
3509        SMALLINT = auto()
3510        SMALLMONEY = auto()
3511        SMALLSERIAL = auto()
3512        STRUCT = auto()
3513        SUPER = auto()
3514        TEXT = auto()
3515        TIME = auto()
3516        TIMETZ = auto()
3517        TIMESTAMP = auto()
3518        TIMESTAMPLTZ = auto()
3519        TIMESTAMPTZ = auto()
3520        TINYINT = auto()
3521        TSMULTIRANGE = auto()
3522        TSRANGE = auto()
3523        TSTZMULTIRANGE = auto()
3524        TSTZRANGE = auto()
3525        UBIGINT = auto()
3526        UINT = auto()
3527        UINT128 = auto()
3528        UINT256 = auto()
3529        UNIQUEIDENTIFIER = auto()
3530        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3531        USERDEFINED = "USER-DEFINED"
3532        USMALLINT = auto()
3533        UTINYINT = auto()
3534        UUID = auto()
3535        VARBINARY = auto()
3536        VARCHAR = auto()
3537        VARIANT = auto()
3538        XML = auto()
3539        YEAR = auto()
3540
3541    TEXT_TYPES = {
3542        Type.CHAR,
3543        Type.NCHAR,
3544        Type.VARCHAR,
3545        Type.NVARCHAR,
3546        Type.TEXT,
3547    }
3548
3549    INTEGER_TYPES = {
3550        Type.INT,
3551        Type.TINYINT,
3552        Type.SMALLINT,
3553        Type.BIGINT,
3554        Type.INT128,
3555        Type.INT256,
3556    }
3557
3558    FLOAT_TYPES = {
3559        Type.FLOAT,
3560        Type.DOUBLE,
3561    }
3562
3563    NUMERIC_TYPES = {
3564        *INTEGER_TYPES,
3565        *FLOAT_TYPES,
3566    }
3567
3568    TEMPORAL_TYPES = {
3569        Type.TIME,
3570        Type.TIMETZ,
3571        Type.TIMESTAMP,
3572        Type.TIMESTAMPTZ,
3573        Type.TIMESTAMPLTZ,
3574        Type.DATE,
3575        Type.DATETIME,
3576        Type.DATETIME64,
3577    }
3578
3579    @classmethod
3580    def build(
3581        cls,
3582        dtype: str | DataType | DataType.Type,
3583        dialect: DialectType = None,
3584        udt: bool = False,
3585        **kwargs,
3586    ) -> DataType:
3587        """
3588        Constructs a DataType object.
3589
3590        Args:
3591            dtype: the data type of interest.
3592            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3593            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3594                DataType, thus creating a user-defined type.
3595            kawrgs: additional arguments to pass in the constructor of DataType.
3596
3597        Returns:
3598            The constructed DataType object.
3599        """
3600        from sqlglot import parse_one
3601
3602        if isinstance(dtype, str):
3603            if dtype.upper() == "UNKNOWN":
3604                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3605
3606            try:
3607                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
3608            except ParseError:
3609                if udt:
3610                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3611                raise
3612        elif isinstance(dtype, DataType.Type):
3613            data_type_exp = DataType(this=dtype)
3614        elif isinstance(dtype, DataType):
3615            return dtype
3616        else:
3617            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3618
3619        return DataType(**{**data_type_exp.args, **kwargs})
3620
3621    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3622        """
3623        Checks whether this DataType matches one of the provided data types. Nested types or precision
3624        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3625
3626        Args:
3627            dtypes: the data types to compare this DataType to.
3628
3629        Returns:
3630            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3631        """
3632        for dtype in dtypes:
3633            other = DataType.build(dtype, udt=True)
3634
3635            if (
3636                other.expressions
3637                or self.this == DataType.Type.USERDEFINED
3638                or other.this == DataType.Type.USERDEFINED
3639            ):
3640                matches = self == other
3641            else:
3642                matches = self.this == other.this
3643
3644            if matches:
3645                return True
3646        return False
3647
3648
3649# https://www.postgresql.org/docs/15/datatype-pseudo.html
3650class PseudoType(Expression):
3651    pass
3652
3653
3654# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3655class SubqueryPredicate(Predicate):
3656    pass
3657
3658
3659class All(SubqueryPredicate):
3660    pass
3661
3662
3663class Any(SubqueryPredicate):
3664    pass
3665
3666
3667class Exists(SubqueryPredicate):
3668    pass
3669
3670
3671# Commands to interact with the databases or engines. For most of the command
3672# expressions we parse whatever comes after the command's name as a string.
3673class Command(Expression):
3674    arg_types = {"this": True, "expression": False}
3675
3676
3677class Transaction(Expression):
3678    arg_types = {"this": False, "modes": False, "mark": False}
3679
3680
3681class Commit(Expression):
3682    arg_types = {"chain": False, "this": False, "durability": False}
3683
3684
3685class Rollback(Expression):
3686    arg_types = {"savepoint": False, "this": False}
3687
3688
3689class AlterTable(Expression):
3690    arg_types = {"this": True, "actions": True, "exists": False}
3691
3692
3693class AddConstraint(Expression):
3694    arg_types = {"this": False, "expression": False, "enforced": False}
3695
3696
3697class DropPartition(Expression):
3698    arg_types = {"expressions": True, "exists": False}
3699
3700
3701# Binary expressions like (ADD a b)
3702class Binary(Condition):
3703    arg_types = {"this": True, "expression": True}
3704
3705    @property
3706    def left(self):
3707        return self.this
3708
3709    @property
3710    def right(self):
3711        return self.expression
3712
3713
3714class Add(Binary):
3715    pass
3716
3717
3718class Connector(Binary):
3719    pass
3720
3721
3722class And(Connector):
3723    pass
3724
3725
3726class Or(Connector):
3727    pass
3728
3729
3730class BitwiseAnd(Binary):
3731    pass
3732
3733
3734class BitwiseLeftShift(Binary):
3735    pass
3736
3737
3738class BitwiseOr(Binary):
3739    pass
3740
3741
3742class BitwiseRightShift(Binary):
3743    pass
3744
3745
3746class BitwiseXor(Binary):
3747    pass
3748
3749
3750class Div(Binary):
3751    pass
3752
3753
3754class Overlaps(Binary):
3755    pass
3756
3757
3758class Dot(Binary):
3759    @property
3760    def name(self) -> str:
3761        return self.expression.name
3762
3763    @property
3764    def output_name(self) -> str:
3765        return self.name
3766
3767    @classmethod
3768    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3769        """Build a Dot object with a sequence of expressions."""
3770        if len(expressions) < 2:
3771            raise ValueError(f"Dot requires >= 2 expressions.")
3772
3773        a, b, *expressions = expressions
3774        dot = Dot(this=a, expression=b)
3775
3776        for expression in expressions:
3777            dot = Dot(this=dot, expression=expression)
3778
3779        return dot
3780
3781
3782class DPipe(Binary):
3783    pass
3784
3785
3786class SafeDPipe(DPipe):
3787    pass
3788
3789
3790class EQ(Binary, Predicate):
3791    pass
3792
3793
3794class NullSafeEQ(Binary, Predicate):
3795    pass
3796
3797
3798class NullSafeNEQ(Binary, Predicate):
3799    pass
3800
3801
3802class Distance(Binary):
3803    pass
3804
3805
3806class Escape(Binary):
3807    pass
3808
3809
3810class Glob(Binary, Predicate):
3811    pass
3812
3813
3814class GT(Binary, Predicate):
3815    pass
3816
3817
3818class GTE(Binary, Predicate):
3819    pass
3820
3821
3822class ILike(Binary, Predicate):
3823    pass
3824
3825
3826class ILikeAny(Binary, Predicate):
3827    pass
3828
3829
3830class IntDiv(Binary):
3831    pass
3832
3833
3834class Is(Binary, Predicate):
3835    pass
3836
3837
3838class Kwarg(Binary):
3839    """Kwarg in special functions like func(kwarg => y)."""
3840
3841
3842class Like(Binary, Predicate):
3843    pass
3844
3845
3846class LikeAny(Binary, Predicate):
3847    pass
3848
3849
3850class LT(Binary, Predicate):
3851    pass
3852
3853
3854class LTE(Binary, Predicate):
3855    pass
3856
3857
3858class Mod(Binary):
3859    pass
3860
3861
3862class Mul(Binary):
3863    pass
3864
3865
3866class NEQ(Binary, Predicate):
3867    pass
3868
3869
3870class SimilarTo(Binary, Predicate):
3871    pass
3872
3873
3874class Slice(Binary):
3875    arg_types = {"this": False, "expression": False}
3876
3877
3878class Sub(Binary):
3879    pass
3880
3881
3882class ArrayOverlaps(Binary):
3883    pass
3884
3885
3886# Unary Expressions
3887# (NOT a)
3888class Unary(Condition):
3889    pass
3890
3891
3892class BitwiseNot(Unary):
3893    pass
3894
3895
3896class Not(Unary):
3897    pass
3898
3899
3900class Paren(Unary):
3901    arg_types = {"this": True, "with": False}
3902
3903    @property
3904    def output_name(self) -> str:
3905        return self.this.name
3906
3907
3908class Neg(Unary):
3909    pass
3910
3911
3912class Alias(Expression):
3913    arg_types = {"this": True, "alias": False}
3914
3915    @property
3916    def output_name(self) -> str:
3917        return self.alias
3918
3919
3920class Aliases(Expression):
3921    arg_types = {"this": True, "expressions": True}
3922
3923    @property
3924    def aliases(self):
3925        return self.expressions
3926
3927
3928class AtTimeZone(Expression):
3929    arg_types = {"this": True, "zone": True}
3930
3931
3932class Between(Predicate):
3933    arg_types = {"this": True, "low": True, "high": True}
3934
3935
3936class Bracket(Condition):
3937    arg_types = {"this": True, "expressions": True}
3938
3939
3940class SafeBracket(Bracket):
3941    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
3942
3943
3944class Distinct(Expression):
3945    arg_types = {"expressions": False, "on": False}
3946
3947
3948class In(Predicate):
3949    arg_types = {
3950        "this": True,
3951        "expressions": False,
3952        "query": False,
3953        "unnest": False,
3954        "field": False,
3955        "is_global": False,
3956    }
3957
3958
3959class TimeUnit(Expression):
3960    """Automatically converts unit arg into a var."""
3961
3962    arg_types = {"unit": False}
3963
3964    def __init__(self, **args):
3965        unit = args.get("unit")
3966        if isinstance(unit, (Column, Literal)):
3967            args["unit"] = Var(this=unit.name)
3968        elif isinstance(unit, Week):
3969            unit.set("this", Var(this=unit.this.name))
3970
3971        super().__init__(**args)
3972
3973
3974# https://www.oracletutorial.com/oracle-basics/oracle-interval/
3975# https://trino.io/docs/current/language/types.html#interval-year-to-month
3976class IntervalYearToMonthSpan(Expression):
3977    arg_types = {}
3978
3979
3980# https://www.oracletutorial.com/oracle-basics/oracle-interval/
3981# https://trino.io/docs/current/language/types.html#interval-day-to-second
3982class IntervalDayToSecondSpan(Expression):
3983    arg_types = {}
3984
3985
3986class Interval(TimeUnit):
3987    arg_types = {"this": False, "unit": False}
3988
3989    @property
3990    def unit(self) -> t.Optional[Var]:
3991        return self.args.get("unit")
3992
3993
3994class IgnoreNulls(Expression):
3995    pass
3996
3997
3998class RespectNulls(Expression):
3999    pass
4000
4001
4002# Functions
4003class Func(Condition):
4004    """
4005    The base class for all function expressions.
4006
4007    Attributes:
4008        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4009            treated as a variable length argument and the argument's value will be stored as a list.
4010        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4011            for this function expression. These values are used to map this node to a name during parsing
4012            as well as to provide the function's name during SQL string generation. By default the SQL
4013            name is set to the expression's class name transformed to snake case.
4014    """
4015
4016    is_var_len_args = False
4017
4018    @classmethod
4019    def from_arg_list(cls, args):
4020        if cls.is_var_len_args:
4021            all_arg_keys = list(cls.arg_types)
4022            # If this function supports variable length argument treat the last argument as such.
4023            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4024            num_non_var = len(non_var_len_arg_keys)
4025
4026            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4027            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4028        else:
4029            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4030
4031        return cls(**args_dict)
4032
4033    @classmethod
4034    def sql_names(cls):
4035        if cls is Func:
4036            raise NotImplementedError(
4037                "SQL name is only supported by concrete function implementations"
4038            )
4039        if "_sql_names" not in cls.__dict__:
4040            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4041        return cls._sql_names
4042
4043    @classmethod
4044    def sql_name(cls):
4045        return cls.sql_names()[0]
4046
4047    @classmethod
4048    def default_parser_mappings(cls):
4049        return {name: cls.from_arg_list for name in cls.sql_names()}
4050
4051
4052class AggFunc(Func):
4053    pass
4054
4055
4056class ParameterizedAgg(AggFunc):
4057    arg_types = {"this": True, "expressions": True, "params": True}
4058
4059
4060class Abs(Func):
4061    pass
4062
4063
4064# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4065class Transform(Func):
4066    arg_types = {"this": True, "expression": True}
4067
4068
4069class Anonymous(Func):
4070    arg_types = {"this": True, "expressions": False}
4071    is_var_len_args = True
4072
4073
4074# https://docs.snowflake.com/en/sql-reference/functions/hll
4075# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4076class Hll(AggFunc):
4077    arg_types = {"this": True, "expressions": False}
4078    is_var_len_args = True
4079
4080
4081class ApproxDistinct(AggFunc):
4082    arg_types = {"this": True, "accuracy": False}
4083    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4084
4085
4086class Array(Func):
4087    arg_types = {"expressions": False}
4088    is_var_len_args = True
4089
4090
4091# https://docs.snowflake.com/en/sql-reference/functions/to_char
4092class ToChar(Func):
4093    arg_types = {"this": True, "format": False}
4094
4095
4096class GenerateSeries(Func):
4097    arg_types = {"start": True, "end": True, "step": False}
4098
4099
4100class ArrayAgg(AggFunc):
4101    pass
4102
4103
4104class ArrayAll(Func):
4105    arg_types = {"this": True, "expression": True}
4106
4107
4108class ArrayAny(Func):
4109    arg_types = {"this": True, "expression": True}
4110
4111
4112class ArrayConcat(Func):
4113    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4114    arg_types = {"this": True, "expressions": False}
4115    is_var_len_args = True
4116
4117
4118class ArrayContains(Binary, Func):
4119    pass
4120
4121
4122class ArrayContained(Binary):
4123    pass
4124
4125
4126class ArrayFilter(Func):
4127    arg_types = {"this": True, "expression": True}
4128    _sql_names = ["FILTER", "ARRAY_FILTER"]
4129
4130
4131class ArrayJoin(Func):
4132    arg_types = {"this": True, "expression": True, "null": False}
4133
4134
4135class ArraySize(Func):
4136    arg_types = {"this": True, "expression": False}
4137
4138
4139class ArraySort(Func):
4140    arg_types = {"this": True, "expression": False}
4141
4142
4143class ArraySum(Func):
4144    pass
4145
4146
4147class ArrayUnionAgg(AggFunc):
4148    pass
4149
4150
4151class Avg(AggFunc):
4152    pass
4153
4154
4155class AnyValue(AggFunc):
4156    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4157
4158
4159class First(Func):
4160    arg_types = {"this": True, "ignore_nulls": False}
4161
4162
4163class Last(Func):
4164    arg_types = {"this": True, "ignore_nulls": False}
4165
4166
4167class Case(Func):
4168    arg_types = {"this": False, "ifs": True, "default": False}
4169
4170    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4171        instance = maybe_copy(self, copy)
4172        instance.append(
4173            "ifs",
4174            If(
4175                this=maybe_parse(condition, copy=copy, **opts),
4176                true=maybe_parse(then, copy=copy, **opts),
4177            ),
4178        )
4179        return instance
4180
4181    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4182        instance = maybe_copy(self, copy)
4183        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4184        return instance
4185
4186
4187class Cast(Func):
4188    arg_types = {"this": True, "to": True, "format": False}
4189
4190    @property
4191    def name(self) -> str:
4192        return self.this.name
4193
4194    @property
4195    def to(self) -> DataType:
4196        return self.args["to"]
4197
4198    @property
4199    def output_name(self) -> str:
4200        return self.name
4201
4202    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4203        """
4204        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4205        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4206        array<int> != array<float>.
4207
4208        Args:
4209            dtypes: the data types to compare this Cast's DataType to.
4210
4211        Returns:
4212            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4213        """
4214        return self.to.is_type(*dtypes)
4215
4216
4217class TryCast(Cast):
4218    pass
4219
4220
4221class CastToStrType(Func):
4222    arg_types = {"this": True, "to": True}
4223
4224
4225class Collate(Binary):
4226    pass
4227
4228
4229class Ceil(Func):
4230    arg_types = {"this": True, "decimals": False}
4231    _sql_names = ["CEIL", "CEILING"]
4232
4233
4234class Coalesce(Func):
4235    arg_types = {"this": True, "expressions": False}
4236    is_var_len_args = True
4237    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4238
4239
4240class Concat(Func):
4241    arg_types = {"expressions": True}
4242    is_var_len_args = True
4243
4244
4245class SafeConcat(Concat):
4246    pass
4247
4248
4249class ConcatWs(Concat):
4250    _sql_names = ["CONCAT_WS"]
4251
4252
4253class Count(AggFunc):
4254    arg_types = {"this": False, "expressions": False}
4255    is_var_len_args = True
4256
4257
4258class CountIf(AggFunc):
4259    pass
4260
4261
4262class CurrentDate(Func):
4263    arg_types = {"this": False}
4264
4265
4266class CurrentDatetime(Func):
4267    arg_types = {"this": False}
4268
4269
4270class CurrentTime(Func):
4271    arg_types = {"this": False}
4272
4273
4274class CurrentTimestamp(Func):
4275    arg_types = {"this": False}
4276
4277
4278class CurrentUser(Func):
4279    arg_types = {"this": False}
4280
4281
4282class DateAdd(Func, TimeUnit):
4283    arg_types = {"this": True, "expression": True, "unit": False}
4284
4285
4286class DateSub(Func, TimeUnit):
4287    arg_types = {"this": True, "expression": True, "unit": False}
4288
4289
4290class DateDiff(Func, TimeUnit):
4291    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4292    arg_types = {"this": True, "expression": True, "unit": False}
4293
4294
4295class DateTrunc(Func):
4296    arg_types = {"unit": True, "this": True, "zone": False}
4297
4298
4299class DatetimeAdd(Func, TimeUnit):
4300    arg_types = {"this": True, "expression": True, "unit": False}
4301
4302
4303class DatetimeSub(Func, TimeUnit):
4304    arg_types = {"this": True, "expression": True, "unit": False}
4305
4306
4307class DatetimeDiff(Func, TimeUnit):
4308    arg_types = {"this": True, "expression": True, "unit": False}
4309
4310
4311class DatetimeTrunc(Func, TimeUnit):
4312    arg_types = {"this": True, "unit": True, "zone": False}
4313
4314
4315class DayOfWeek(Func):
4316    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4317
4318
4319class DayOfMonth(Func):
4320    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4321
4322
4323class DayOfYear(Func):
4324    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4325
4326
4327class WeekOfYear(Func):
4328    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4329
4330
4331class MonthsBetween(Func):
4332    arg_types = {"this": True, "expression": True, "roundoff": False}
4333
4334
4335class LastDateOfMonth(Func):
4336    pass
4337
4338
4339class Extract(Func):
4340    arg_types = {"this": True, "expression": True}
4341
4342
4343class TimestampAdd(Func, TimeUnit):
4344    arg_types = {"this": True, "expression": True, "unit": False}
4345
4346
4347class TimestampSub(Func, TimeUnit):
4348    arg_types = {"this": True, "expression": True, "unit": False}
4349
4350
4351class TimestampDiff(Func, TimeUnit):
4352    arg_types = {"this": True, "expression": True, "unit": False}
4353
4354
4355class TimestampTrunc(Func, TimeUnit):
4356    arg_types = {"this": True, "unit": True, "zone": False}
4357
4358
4359class TimeAdd(Func, TimeUnit):
4360    arg_types = {"this": True, "expression": True, "unit": False}
4361
4362
4363class TimeSub(Func, TimeUnit):
4364    arg_types = {"this": True, "expression": True, "unit": False}
4365
4366
4367class TimeDiff(Func, TimeUnit):
4368    arg_types = {"this": True, "expression": True, "unit": False}
4369
4370
4371class TimeTrunc(Func, TimeUnit):
4372    arg_types = {"this": True, "unit": True, "zone": False}
4373
4374
4375class DateFromParts(Func):
4376    _sql_names = ["DATEFROMPARTS"]
4377    arg_types = {"year": True, "month": True, "day": True}
4378
4379
4380class DateStrToDate(Func):
4381    pass
4382
4383
4384class DateToDateStr(Func):
4385    pass
4386
4387
4388class DateToDi(Func):
4389    pass
4390
4391
4392# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4393class Date(Func):
4394    arg_types = {"this": True, "zone": False}
4395
4396
4397class Day(Func):
4398    pass
4399
4400
4401class Decode(Func):
4402    arg_types = {"this": True, "charset": True, "replace": False}
4403
4404
4405class DiToDate(Func):
4406    pass
4407
4408
4409class Encode(Func):
4410    arg_types = {"this": True, "charset": True}
4411
4412
4413class Exp(Func):
4414    pass
4415
4416
4417class Explode(Func):
4418    pass
4419
4420
4421class Floor(Func):
4422    arg_types = {"this": True, "decimals": False}
4423
4424
4425class FromBase64(Func):
4426    pass
4427
4428
4429class ToBase64(Func):
4430    pass
4431
4432
4433class Greatest(Func):
4434    arg_types = {"this": True, "expressions": False}
4435    is_var_len_args = True
4436
4437
4438class GroupConcat(Func):
4439    arg_types = {"this": True, "separator": False}
4440
4441
4442class Hex(Func):
4443    pass
4444
4445
4446class Xor(Connector, Func):
4447    arg_types = {"this": False, "expression": False, "expressions": False}
4448
4449
4450class If(Func):
4451    arg_types = {"this": True, "true": True, "false": False}
4452
4453
4454class Initcap(Func):
4455    arg_types = {"this": True, "expression": False}
4456
4457
4458class IsNan(Func):
4459    _sql_names = ["IS_NAN", "ISNAN"]
4460
4461
4462class JSONKeyValue(Expression):
4463    arg_types = {"this": True, "expression": True}
4464
4465
4466class JSONObject(Func):
4467    arg_types = {
4468        "expressions": False,
4469        "null_handling": False,
4470        "unique_keys": False,
4471        "return_type": False,
4472        "format_json": False,
4473        "encoding": False,
4474    }
4475
4476
4477class OpenJSONColumnDef(Expression):
4478    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4479
4480
4481class OpenJSON(Func):
4482    arg_types = {"this": True, "path": False, "expressions": False}
4483
4484
4485class JSONBContains(Binary):
4486    _sql_names = ["JSONB_CONTAINS"]
4487
4488
4489class JSONExtract(Binary, Func):
4490    _sql_names = ["JSON_EXTRACT"]
4491
4492
4493class JSONExtractScalar(JSONExtract):
4494    _sql_names = ["JSON_EXTRACT_SCALAR"]
4495
4496
4497class JSONBExtract(JSONExtract):
4498    _sql_names = ["JSONB_EXTRACT"]
4499
4500
4501class JSONBExtractScalar(JSONExtract):
4502    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4503
4504
4505class JSONFormat(Func):
4506    arg_types = {"this": False, "options": False}
4507    _sql_names = ["JSON_FORMAT"]
4508
4509
4510# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4511class JSONArrayContains(Binary, Predicate, Func):
4512    _sql_names = ["JSON_ARRAY_CONTAINS"]
4513
4514
4515class Least(Func):
4516    arg_types = {"this": True, "expressions": False}
4517    is_var_len_args = True
4518
4519
4520class Left(Func):
4521    arg_types = {"this": True, "expression": True}
4522
4523
4524class Right(Func):
4525    arg_types = {"this": True, "expression": True}
4526
4527
4528class Length(Func):
4529    _sql_names = ["LENGTH", "LEN"]
4530
4531
4532class Levenshtein(Func):
4533    arg_types = {
4534        "this": True,
4535        "expression": False,
4536        "ins_cost": False,
4537        "del_cost": False,
4538        "sub_cost": False,
4539    }
4540
4541
4542class Ln(Func):
4543    pass
4544
4545
4546class Log(Func):
4547    arg_types = {"this": True, "expression": False}
4548
4549
4550class Log2(Func):
4551    pass
4552
4553
4554class Log10(Func):
4555    pass
4556
4557
4558class LogicalOr(AggFunc):
4559    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4560
4561
4562class LogicalAnd(AggFunc):
4563    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4564
4565
4566class Lower(Func):
4567    _sql_names = ["LOWER", "LCASE"]
4568
4569
4570class Map(Func):
4571    arg_types = {"keys": False, "values": False}
4572
4573
4574class MapFromEntries(Func):
4575    pass
4576
4577
4578class StarMap(Func):
4579    pass
4580
4581
4582class VarMap(Func):
4583    arg_types = {"keys": True, "values": True}
4584    is_var_len_args = True
4585
4586    @property
4587    def keys(self) -> t.List[Expression]:
4588        return self.args["keys"].expressions
4589
4590    @property
4591    def values(self) -> t.List[Expression]:
4592        return self.args["values"].expressions
4593
4594
4595# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4596class MatchAgainst(Func):
4597    arg_types = {"this": True, "expressions": True, "modifier": False}
4598
4599
4600class Max(AggFunc):
4601    arg_types = {"this": True, "expressions": False}
4602    is_var_len_args = True
4603
4604
4605class MD5(Func):
4606    _sql_names = ["MD5"]
4607
4608
4609# Represents the variant of the MD5 function that returns a binary value
4610class MD5Digest(Func):
4611    _sql_names = ["MD5_DIGEST"]
4612
4613
4614class Min(AggFunc):
4615    arg_types = {"this": True, "expressions": False}
4616    is_var_len_args = True
4617
4618
4619class Month(Func):
4620    pass
4621
4622
4623class Nvl2(Func):
4624    arg_types = {"this": True, "true": True, "false": False}
4625
4626
4627class Posexplode(Func):
4628    pass
4629
4630
4631class Pow(Binary, Func):
4632    _sql_names = ["POWER", "POW"]
4633
4634
4635class PercentileCont(AggFunc):
4636    arg_types = {"this": True, "expression": False}
4637
4638
4639class PercentileDisc(AggFunc):
4640    arg_types = {"this": True, "expression": False}
4641
4642
4643class Quantile(AggFunc):
4644    arg_types = {"this": True, "quantile": True}
4645
4646
4647class ApproxQuantile(Quantile):
4648    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4649
4650
4651class RangeN(Func):
4652    arg_types = {"this": True, "expressions": True, "each": False}
4653
4654
4655class ReadCSV(Func):
4656    _sql_names = ["READ_CSV"]
4657    is_var_len_args = True
4658    arg_types = {"this": True, "expressions": False}
4659
4660
4661class Reduce(Func):
4662    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4663
4664
4665class RegexpExtract(Func):
4666    arg_types = {
4667        "this": True,
4668        "expression": True,
4669        "position": False,
4670        "occurrence": False,
4671        "parameters": False,
4672        "group": False,
4673    }
4674
4675
4676class RegexpReplace(Func):
4677    arg_types = {
4678        "this": True,
4679        "expression": True,
4680        "replacement": True,
4681        "position": False,
4682        "occurrence": False,
4683        "parameters": False,
4684    }
4685
4686
4687class RegexpLike(Binary, Func):
4688    arg_types = {"this": True, "expression": True, "flag": False}
4689
4690
4691class RegexpILike(Func):
4692    arg_types = {"this": True, "expression": True, "flag": False}
4693
4694
4695# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4696# limit is the number of times a pattern is applied
4697class RegexpSplit(Func):
4698    arg_types = {"this": True, "expression": True, "limit": False}
4699
4700
4701class Repeat(Func):
4702    arg_types = {"this": True, "times": True}
4703
4704
4705class Round(Func):
4706    arg_types = {"this": True, "decimals": False}
4707
4708
4709class RowNumber(Func):
4710    arg_types: t.Dict[str, t.Any] = {}
4711
4712
4713class SafeDivide(Func):
4714    arg_types = {"this": True, "expression": True}
4715
4716
4717class SetAgg(AggFunc):
4718    pass
4719
4720
4721class SHA(Func):
4722    _sql_names = ["SHA", "SHA1"]
4723
4724
4725class SHA2(Func):
4726    _sql_names = ["SHA2"]
4727    arg_types = {"this": True, "length": False}
4728
4729
4730class SortArray(Func):
4731    arg_types = {"this": True, "asc": False}
4732
4733
4734class Split(Func):
4735    arg_types = {"this": True, "expression": True, "limit": False}
4736
4737
4738# Start may be omitted in the case of postgres
4739# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
4740class Substring(Func):
4741    arg_types = {"this": True, "start": False, "length": False}
4742
4743
4744class StandardHash(Func):
4745    arg_types = {"this": True, "expression": False}
4746
4747
4748class StartsWith(Func):
4749    _sql_names = ["STARTS_WITH", "STARTSWITH"]
4750    arg_types = {"this": True, "expression": True}
4751
4752
4753class StrPosition(Func):
4754    arg_types = {
4755        "this": True,
4756        "substr": True,
4757        "position": False,
4758        "instance": False,
4759    }
4760
4761
4762class StrToDate(Func):
4763    arg_types = {"this": True, "format": True}
4764
4765
4766class StrToTime(Func):
4767    arg_types = {"this": True, "format": True, "zone": False}
4768
4769
4770# Spark allows unix_timestamp()
4771# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
4772class StrToUnix(Func):
4773    arg_types = {"this": False, "format": False}
4774
4775
4776# https://prestodb.io/docs/current/functions/string.html
4777# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
4778class StrToMap(Func):
4779    arg_types = {
4780        "this": True,
4781        "pair_delim": False,
4782        "key_value_delim": False,
4783        "duplicate_resolution_callback": False,
4784    }
4785
4786
4787class NumberToStr(Func):
4788    arg_types = {"this": True, "format": True, "culture": False}
4789
4790
4791class FromBase(Func):
4792    arg_types = {"this": True, "expression": True}
4793
4794
4795class Struct(Func):
4796    arg_types = {"expressions": True}
4797    is_var_len_args = True
4798
4799
4800class StructExtract(Func):
4801    arg_types = {"this": True, "expression": True}
4802
4803
4804# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
4805# https://docs.snowflake.com/en/sql-reference/functions/insert
4806class Stuff(Func):
4807    _sql_names = ["STUFF", "INSERT"]
4808    arg_types = {"this": True, "start": True, "length": True, "expression": True}
4809
4810
4811class Sum(AggFunc):
4812    pass
4813
4814
4815class Sqrt(Func):
4816    pass
4817
4818
4819class Stddev(AggFunc):
4820    pass
4821
4822
4823class StddevPop(AggFunc):
4824    pass
4825
4826
4827class StddevSamp(AggFunc):
4828    pass
4829
4830
4831class TimeToStr(Func):
4832    arg_types = {"this": True, "format": True, "culture": False}
4833
4834
4835class TimeToTimeStr(Func):
4836    pass
4837
4838
4839class TimeToUnix(Func):
4840    pass
4841
4842
4843class TimeStrToDate(Func):
4844    pass
4845
4846
4847class TimeStrToTime(Func):
4848    pass
4849
4850
4851class TimeStrToUnix(Func):
4852    pass
4853
4854
4855class Trim(Func):
4856    arg_types = {
4857        "this": True,
4858        "expression": False,
4859        "position": False,
4860        "collation": False,
4861    }
4862
4863
4864class TsOrDsAdd(Func, TimeUnit):
4865    arg_types = {"this": True, "expression": True, "unit": False}
4866
4867
4868class TsOrDsToDateStr(Func):
4869    pass
4870
4871
4872class TsOrDsToDate(Func):
4873    arg_types = {"this": True, "format": False}
4874
4875
4876class TsOrDiToDi(Func):
4877    pass
4878
4879
4880class Unhex(Func):
4881    pass
4882
4883
4884class UnixToStr(Func):
4885    arg_types = {"this": True, "format": False}
4886
4887
4888# https://prestodb.io/docs/current/functions/datetime.html
4889# presto has weird zone/hours/minutes
4890class UnixToTime(Func):
4891    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
4892
4893    SECONDS = Literal.string("seconds")
4894    MILLIS = Literal.string("millis")
4895    MICROS = Literal.string("micros")
4896
4897
4898class UnixToTimeStr(Func):
4899    pass
4900
4901
4902class Upper(Func):
4903    _sql_names = ["UPPER", "UCASE"]
4904
4905
4906class Variance(AggFunc):
4907    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
4908
4909
4910class VariancePop(AggFunc):
4911    _sql_names = ["VARIANCE_POP", "VAR_POP"]
4912
4913
4914class Week(Func):
4915    arg_types = {"this": True, "mode": False}
4916
4917
4918class XMLTable(Func):
4919    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
4920
4921
4922class Year(Func):
4923    pass
4924
4925
4926class Use(Expression):
4927    arg_types = {"this": True, "kind": False}
4928
4929
4930class Merge(Expression):
4931    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
4932
4933
4934class When(Func):
4935    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
4936
4937
4938# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
4939# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
4940class NextValueFor(Func):
4941    arg_types = {"this": True, "order": False}
4942
4943
4944def _norm_arg(arg):
4945    return arg.lower() if type(arg) is str else arg
4946
4947
4948ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
4949
4950
4951# Helpers
4952@t.overload
4953def maybe_parse(
4954    sql_or_expression: ExpOrStr,
4955    *,
4956    into: t.Type[E],
4957    dialect: DialectType = None,
4958    prefix: t.Optional[str] = None,
4959    copy: bool = False,
4960    **opts,
4961) -> E:
4962    ...
4963
4964
4965@t.overload
4966def maybe_parse(
4967    sql_or_expression: str | E,
4968    *,
4969    into: t.Optional[IntoType] = None,
4970    dialect: DialectType = None,
4971    prefix: t.Optional[str] = None,
4972    copy: bool = False,
4973    **opts,
4974) -> E:
4975    ...
4976
4977
4978def maybe_parse(
4979    sql_or_expression: ExpOrStr,
4980    *,
4981    into: t.Optional[IntoType] = None,
4982    dialect: DialectType = None,
4983    prefix: t.Optional[str] = None,
4984    copy: bool = False,
4985    **opts,
4986) -> Expression:
4987    """Gracefully handle a possible string or expression.
4988
4989    Example:
4990        >>> maybe_parse("1")
4991        (LITERAL this: 1, is_string: False)
4992        >>> maybe_parse(to_identifier("x"))
4993        (IDENTIFIER this: x, quoted: False)
4994
4995    Args:
4996        sql_or_expression: the SQL code string or an expression
4997        into: the SQLGlot Expression to parse into
4998        dialect: the dialect used to parse the input expressions (in the case that an
4999            input expression is a SQL string).
5000        prefix: a string to prefix the sql with before it gets parsed
5001            (automatically includes a space)
5002        copy: whether or not to copy the expression.
5003        **opts: other options to use to parse the input expressions (again, in the case
5004            that an input expression is a SQL string).
5005
5006    Returns:
5007        Expression: the parsed or given expression.
5008    """
5009    if isinstance(sql_or_expression, Expression):
5010        if copy:
5011            return sql_or_expression.copy()
5012        return sql_or_expression
5013
5014    if sql_or_expression is None:
5015        raise ParseError(f"SQL cannot be None")
5016
5017    import sqlglot
5018
5019    sql = str(sql_or_expression)
5020    if prefix:
5021        sql = f"{prefix} {sql}"
5022
5023    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5024
5025
5026@t.overload
5027def maybe_copy(instance: None, copy: bool = True) -> None:
5028    ...
5029
5030
5031@t.overload
5032def maybe_copy(instance: E, copy: bool = True) -> E:
5033    ...
5034
5035
5036def maybe_copy(instance, copy=True):
5037    return instance.copy() if copy and instance else instance
5038
5039
5040def _is_wrong_expression(expression, into):
5041    return isinstance(expression, Expression) and not isinstance(expression, into)
5042
5043
5044def _apply_builder(
5045    expression,
5046    instance,
5047    arg,
5048    copy=True,
5049    prefix=None,
5050    into=None,
5051    dialect=None,
5052    **opts,
5053):
5054    if _is_wrong_expression(expression, into):
5055        expression = into(this=expression)
5056    instance = maybe_copy(instance, copy)
5057    expression = maybe_parse(
5058        sql_or_expression=expression,
5059        prefix=prefix,
5060        into=into,
5061        dialect=dialect,
5062        **opts,
5063    )
5064    instance.set(arg, expression)
5065    return instance
5066
5067
5068def _apply_child_list_builder(
5069    *expressions,
5070    instance,
5071    arg,
5072    append=True,
5073    copy=True,
5074    prefix=None,
5075    into=None,
5076    dialect=None,
5077    properties=None,
5078    **opts,
5079):
5080    instance = maybe_copy(instance, copy)
5081    parsed = []
5082    for expression in expressions:
5083        if expression is not None:
5084            if _is_wrong_expression(expression, into):
5085                expression = into(expressions=[expression])
5086
5087            expression = maybe_parse(
5088                expression,
5089                into=into,
5090                dialect=dialect,
5091                prefix=prefix,
5092                **opts,
5093            )
5094            parsed.extend(expression.expressions)
5095
5096    existing = instance.args.get(arg)
5097    if append and existing:
5098        parsed = existing.expressions + parsed
5099
5100    child = into(expressions=parsed)
5101    for k, v in (properties or {}).items():
5102        child.set(k, v)
5103    instance.set(arg, child)
5104
5105    return instance
5106
5107
5108def _apply_list_builder(
5109    *expressions,
5110    instance,
5111    arg,
5112    append=True,
5113    copy=True,
5114    prefix=None,
5115    into=None,
5116    dialect=None,
5117    **opts,
5118):
5119    inst = maybe_copy(instance, copy)
5120
5121    expressions = [
5122        maybe_parse(
5123            sql_or_expression=expression,
5124            into=into,
5125            prefix=prefix,
5126            dialect=dialect,
5127            **opts,
5128        )
5129        for expression in expressions
5130        if expression is not None
5131    ]
5132
5133    existing_expressions = inst.args.get(arg)
5134    if append and existing_expressions:
5135        expressions = existing_expressions + expressions
5136
5137    inst.set(arg, expressions)
5138    return inst
5139
5140
5141def _apply_conjunction_builder(
5142    *expressions,
5143    instance,
5144    arg,
5145    into=None,
5146    append=True,
5147    copy=True,
5148    dialect=None,
5149    **opts,
5150):
5151    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5152    if not expressions:
5153        return instance
5154
5155    inst = maybe_copy(instance, copy)
5156
5157    existing = inst.args.get(arg)
5158    if append and existing is not None:
5159        expressions = [existing.this if into else existing] + list(expressions)
5160
5161    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5162
5163    inst.set(arg, into(this=node) if into else node)
5164    return inst
5165
5166
5167def _apply_cte_builder(
5168    instance: E,
5169    alias: ExpOrStr,
5170    as_: ExpOrStr,
5171    recursive: t.Optional[bool] = None,
5172    append: bool = True,
5173    dialect: DialectType = None,
5174    copy: bool = True,
5175    **opts,
5176) -> E:
5177    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5178    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5179    cte = CTE(this=as_expression, alias=alias_expression)
5180    return _apply_child_list_builder(
5181        cte,
5182        instance=instance,
5183        arg="with",
5184        append=append,
5185        copy=copy,
5186        into=With,
5187        properties={"recursive": recursive or False},
5188    )
5189
5190
5191def _combine(
5192    expressions: t.Sequence[t.Optional[ExpOrStr]],
5193    operator: t.Type[Connector],
5194    dialect: DialectType = None,
5195    copy: bool = True,
5196    **opts,
5197) -> Expression:
5198    conditions = [
5199        condition(expression, dialect=dialect, copy=copy, **opts)
5200        for expression in expressions
5201        if expression is not None
5202    ]
5203
5204    this, *rest = conditions
5205    if rest:
5206        this = _wrap(this, Connector)
5207    for expression in rest:
5208        this = operator(this=this, expression=_wrap(expression, Connector))
5209
5210    return this
5211
5212
5213def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5214    return Paren(this=expression) if isinstance(expression, kind) else expression
5215
5216
5217def union(
5218    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5219) -> Union:
5220    """
5221    Initializes a syntax tree from one UNION expression.
5222
5223    Example:
5224        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5225        'SELECT * FROM foo UNION SELECT * FROM bla'
5226
5227    Args:
5228        left: the SQL code string corresponding to the left-hand side.
5229            If an `Expression` instance is passed, it will be used as-is.
5230        right: the SQL code string corresponding to the right-hand side.
5231            If an `Expression` instance is passed, it will be used as-is.
5232        distinct: set the DISTINCT flag if and only if this is true.
5233        dialect: the dialect used to parse the input expression.
5234        opts: other options to use to parse the input expressions.
5235
5236    Returns:
5237        The new Union instance.
5238    """
5239    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5240    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5241
5242    return Union(this=left, expression=right, distinct=distinct)
5243
5244
5245def intersect(
5246    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5247) -> Intersect:
5248    """
5249    Initializes a syntax tree from one INTERSECT expression.
5250
5251    Example:
5252        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5253        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5254
5255    Args:
5256        left: the SQL code string corresponding to the left-hand side.
5257            If an `Expression` instance is passed, it will be used as-is.
5258        right: the SQL code string corresponding to the right-hand side.
5259            If an `Expression` instance is passed, it will be used as-is.
5260        distinct: set the DISTINCT flag if and only if this is true.
5261        dialect: the dialect used to parse the input expression.
5262        opts: other options to use to parse the input expressions.
5263
5264    Returns:
5265        The new Intersect instance.
5266    """
5267    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5268    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5269
5270    return Intersect(this=left, expression=right, distinct=distinct)
5271
5272
5273def except_(
5274    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5275) -> Except:
5276    """
5277    Initializes a syntax tree from one EXCEPT expression.
5278
5279    Example:
5280        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5281        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5282
5283    Args:
5284        left: the SQL code string corresponding to the left-hand side.
5285            If an `Expression` instance is passed, it will be used as-is.
5286        right: the SQL code string corresponding to the right-hand side.
5287            If an `Expression` instance is passed, it will be used as-is.
5288        distinct: set the DISTINCT flag if and only if this is true.
5289        dialect: the dialect used to parse the input expression.
5290        opts: other options to use to parse the input expressions.
5291
5292    Returns:
5293        The new Except instance.
5294    """
5295    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5296    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5297
5298    return Except(this=left, expression=right, distinct=distinct)
5299
5300
5301def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5302    """
5303    Initializes a syntax tree from one or multiple SELECT expressions.
5304
5305    Example:
5306        >>> select("col1", "col2").from_("tbl").sql()
5307        'SELECT col1, col2 FROM tbl'
5308
5309    Args:
5310        *expressions: the SQL code string to parse as the expressions of a
5311            SELECT statement. If an Expression instance is passed, this is used as-is.
5312        dialect: the dialect used to parse the input expressions (in the case that an
5313            input expression is a SQL string).
5314        **opts: other options to use to parse the input expressions (again, in the case
5315            that an input expression is a SQL string).
5316
5317    Returns:
5318        Select: the syntax tree for the SELECT statement.
5319    """
5320    return Select().select(*expressions, dialect=dialect, **opts)
5321
5322
5323def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5324    """
5325    Initializes a syntax tree from a FROM expression.
5326
5327    Example:
5328        >>> from_("tbl").select("col1", "col2").sql()
5329        'SELECT col1, col2 FROM tbl'
5330
5331    Args:
5332        *expression: the SQL code string to parse as the FROM expressions of a
5333            SELECT statement. If an Expression instance is passed, this is used as-is.
5334        dialect: the dialect used to parse the input expression (in the case that the
5335            input expression is a SQL string).
5336        **opts: other options to use to parse the input expressions (again, in the case
5337            that the input expression is a SQL string).
5338
5339    Returns:
5340        Select: the syntax tree for the SELECT statement.
5341    """
5342    return Select().from_(expression, dialect=dialect, **opts)
5343
5344
5345def update(
5346    table: str | Table,
5347    properties: dict,
5348    where: t.Optional[ExpOrStr] = None,
5349    from_: t.Optional[ExpOrStr] = None,
5350    dialect: DialectType = None,
5351    **opts,
5352) -> Update:
5353    """
5354    Creates an update statement.
5355
5356    Example:
5357        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5358        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5359
5360    Args:
5361        *properties: dictionary of properties to set which are
5362            auto converted to sql objects eg None -> NULL
5363        where: sql conditional parsed into a WHERE statement
5364        from_: sql statement parsed into a FROM statement
5365        dialect: the dialect used to parse the input expressions.
5366        **opts: other options to use to parse the input expressions.
5367
5368    Returns:
5369        Update: the syntax tree for the UPDATE statement.
5370    """
5371    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5372    update_expr.set(
5373        "expressions",
5374        [
5375            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5376            for k, v in properties.items()
5377        ],
5378    )
5379    if from_:
5380        update_expr.set(
5381            "from",
5382            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5383        )
5384    if isinstance(where, Condition):
5385        where = Where(this=where)
5386    if where:
5387        update_expr.set(
5388            "where",
5389            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5390        )
5391    return update_expr
5392
5393
5394def delete(
5395    table: ExpOrStr,
5396    where: t.Optional[ExpOrStr] = None,
5397    returning: t.Optional[ExpOrStr] = None,
5398    dialect: DialectType = None,
5399    **opts,
5400) -> Delete:
5401    """
5402    Builds a delete statement.
5403
5404    Example:
5405        >>> delete("my_table", where="id > 1").sql()
5406        'DELETE FROM my_table WHERE id > 1'
5407
5408    Args:
5409        where: sql conditional parsed into a WHERE statement
5410        returning: sql conditional parsed into a RETURNING statement
5411        dialect: the dialect used to parse the input expressions.
5412        **opts: other options to use to parse the input expressions.
5413
5414    Returns:
5415        Delete: the syntax tree for the DELETE statement.
5416    """
5417    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5418    if where:
5419        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5420    if returning:
5421        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5422    return delete_expr
5423
5424
5425def insert(
5426    expression: ExpOrStr,
5427    into: ExpOrStr,
5428    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5429    overwrite: t.Optional[bool] = None,
5430    dialect: DialectType = None,
5431    copy: bool = True,
5432    **opts,
5433) -> Insert:
5434    """
5435    Builds an INSERT statement.
5436
5437    Example:
5438        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5439        'INSERT INTO tbl VALUES (1, 2, 3)'
5440
5441    Args:
5442        expression: the sql string or expression of the INSERT statement
5443        into: the tbl to insert data to.
5444        columns: optionally the table's column names.
5445        overwrite: whether to INSERT OVERWRITE or not.
5446        dialect: the dialect used to parse the input expressions.
5447        copy: whether or not to copy the expression.
5448        **opts: other options to use to parse the input expressions.
5449
5450    Returns:
5451        Insert: the syntax tree for the INSERT statement.
5452    """
5453    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5454    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5455
5456    if columns:
5457        this = _apply_list_builder(
5458            *columns,
5459            instance=Schema(this=this),
5460            arg="expressions",
5461            into=Identifier,
5462            copy=False,
5463            dialect=dialect,
5464            **opts,
5465        )
5466
5467    return Insert(this=this, expression=expr, overwrite=overwrite)
5468
5469
5470def condition(
5471    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5472) -> Condition:
5473    """
5474    Initialize a logical condition expression.
5475
5476    Example:
5477        >>> condition("x=1").sql()
5478        'x = 1'
5479
5480        This is helpful for composing larger logical syntax trees:
5481        >>> where = condition("x=1")
5482        >>> where = where.and_("y=1")
5483        >>> Select().from_("tbl").select("*").where(where).sql()
5484        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5485
5486    Args:
5487        *expression: the SQL code string to parse.
5488            If an Expression instance is passed, this is used as-is.
5489        dialect: the dialect used to parse the input expression (in the case that the
5490            input expression is a SQL string).
5491        copy: Whether or not to copy `expression` (only applies to expressions).
5492        **opts: other options to use to parse the input expressions (again, in the case
5493            that the input expression is a SQL string).
5494
5495    Returns:
5496        The new Condition instance
5497    """
5498    return maybe_parse(
5499        expression,
5500        into=Condition,
5501        dialect=dialect,
5502        copy=copy,
5503        **opts,
5504    )
5505
5506
5507def and_(
5508    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5509) -> Condition:
5510    """
5511    Combine multiple conditions with an AND logical operator.
5512
5513    Example:
5514        >>> and_("x=1", and_("y=1", "z=1")).sql()
5515        'x = 1 AND (y = 1 AND z = 1)'
5516
5517    Args:
5518        *expressions: the SQL code strings to parse.
5519            If an Expression instance is passed, this is used as-is.
5520        dialect: the dialect used to parse the input expression.
5521        copy: whether or not to copy `expressions` (only applies to Expressions).
5522        **opts: other options to use to parse the input expressions.
5523
5524    Returns:
5525        And: the new condition
5526    """
5527    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5528
5529
5530def or_(
5531    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5532) -> Condition:
5533    """
5534    Combine multiple conditions with an OR logical operator.
5535
5536    Example:
5537        >>> or_("x=1", or_("y=1", "z=1")).sql()
5538        'x = 1 OR (y = 1 OR z = 1)'
5539
5540    Args:
5541        *expressions: the SQL code strings to parse.
5542            If an Expression instance is passed, this is used as-is.
5543        dialect: the dialect used to parse the input expression.
5544        copy: whether or not to copy `expressions` (only applies to Expressions).
5545        **opts: other options to use to parse the input expressions.
5546
5547    Returns:
5548        Or: the new condition
5549    """
5550    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5551
5552
5553def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5554    """
5555    Wrap a condition with a NOT operator.
5556
5557    Example:
5558        >>> not_("this_suit='black'").sql()
5559        "NOT this_suit = 'black'"
5560
5561    Args:
5562        expression: the SQL code string to parse.
5563            If an Expression instance is passed, this is used as-is.
5564        dialect: the dialect used to parse the input expression.
5565        copy: whether to copy the expression or not.
5566        **opts: other options to use to parse the input expressions.
5567
5568    Returns:
5569        The new condition.
5570    """
5571    this = condition(
5572        expression,
5573        dialect=dialect,
5574        copy=copy,
5575        **opts,
5576    )
5577    return Not(this=_wrap(this, Connector))
5578
5579
5580def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5581    """
5582    Wrap an expression in parentheses.
5583
5584    Example:
5585        >>> paren("5 + 3").sql()
5586        '(5 + 3)'
5587
5588    Args:
5589        expression: the SQL code string to parse.
5590            If an Expression instance is passed, this is used as-is.
5591        copy: whether to copy the expression or not.
5592
5593    Returns:
5594        The wrapped expression.
5595    """
5596    return Paren(this=maybe_parse(expression, copy=copy))
5597
5598
5599SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5600
5601
5602@t.overload
5603def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5604    ...
5605
5606
5607@t.overload
5608def to_identifier(
5609    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5610) -> Identifier:
5611    ...
5612
5613
5614def to_identifier(name, quoted=None, copy=True):
5615    """Builds an identifier.
5616
5617    Args:
5618        name: The name to turn into an identifier.
5619        quoted: Whether or not force quote the identifier.
5620        copy: Whether or not to copy a passed in Identefier node.
5621
5622    Returns:
5623        The identifier ast node.
5624    """
5625
5626    if name is None:
5627        return None
5628
5629    if isinstance(name, Identifier):
5630        identifier = maybe_copy(name, copy)
5631    elif isinstance(name, str):
5632        identifier = Identifier(
5633            this=name,
5634            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5635        )
5636    else:
5637        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5638    return identifier
5639
5640
5641INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5642
5643
5644def to_interval(interval: str | Literal) -> Interval:
5645    """Builds an interval expression from a string like '1 day' or '5 months'."""
5646    if isinstance(interval, Literal):
5647        if not interval.is_string:
5648            raise ValueError("Invalid interval string.")
5649
5650        interval = interval.this
5651
5652    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5653
5654    if not interval_parts:
5655        raise ValueError("Invalid interval string.")
5656
5657    return Interval(
5658        this=Literal.string(interval_parts.group(1)),
5659        unit=Var(this=interval_parts.group(2)),
5660    )
5661
5662
5663@t.overload
5664def to_table(sql_path: str | Table, **kwargs) -> Table:
5665    ...
5666
5667
5668@t.overload
5669def to_table(sql_path: None, **kwargs) -> None:
5670    ...
5671
5672
5673def to_table(
5674    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
5675) -> t.Optional[Table]:
5676    """
5677    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
5678    If a table is passed in then that table is returned.
5679
5680    Args:
5681        sql_path: a `[catalog].[schema].[table]` string.
5682        dialect: the source dialect according to which the table name will be parsed.
5683        kwargs: the kwargs to instantiate the resulting `Table` expression with.
5684
5685    Returns:
5686        A table expression.
5687    """
5688    if sql_path is None or isinstance(sql_path, Table):
5689        return sql_path
5690    if not isinstance(sql_path, str):
5691        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
5692
5693    table = maybe_parse(sql_path, into=Table, dialect=dialect)
5694    if table:
5695        for k, v in kwargs.items():
5696            table.set(k, v)
5697
5698    return table
5699
5700
5701def to_column(sql_path: str | Column, **kwargs) -> Column:
5702    """
5703    Create a column from a `[table].[column]` sql path. Schema is optional.
5704
5705    If a column is passed in then that column is returned.
5706
5707    Args:
5708        sql_path: `[table].[column]` string
5709    Returns:
5710        Table: A column expression
5711    """
5712    if sql_path is None or isinstance(sql_path, Column):
5713        return sql_path
5714    if not isinstance(sql_path, str):
5715        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
5716    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
5717
5718
5719def alias_(
5720    expression: ExpOrStr,
5721    alias: str | Identifier,
5722    table: bool | t.Sequence[str | Identifier] = False,
5723    quoted: t.Optional[bool] = None,
5724    dialect: DialectType = None,
5725    copy: bool = True,
5726    **opts,
5727):
5728    """Create an Alias expression.
5729
5730    Example:
5731        >>> alias_('foo', 'bar').sql()
5732        'foo AS bar'
5733
5734        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
5735        '(SELECT 1, 2) AS bar(a, b)'
5736
5737    Args:
5738        expression: the SQL code strings to parse.
5739            If an Expression instance is passed, this is used as-is.
5740        alias: the alias name to use. If the name has
5741            special characters it is quoted.
5742        table: Whether or not to create a table alias, can also be a list of columns.
5743        quoted: whether or not to quote the alias
5744        dialect: the dialect used to parse the input expression.
5745        copy: Whether or not to copy the expression.
5746        **opts: other options to use to parse the input expressions.
5747
5748    Returns:
5749        Alias: the aliased expression
5750    """
5751    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5752    alias = to_identifier(alias, quoted=quoted)
5753
5754    if table:
5755        table_alias = TableAlias(this=alias)
5756        exp.set("alias", table_alias)
5757
5758        if not isinstance(table, bool):
5759            for column in table:
5760                table_alias.append("columns", to_identifier(column, quoted=quoted))
5761
5762        return exp
5763
5764    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
5765    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
5766    # for the complete Window expression.
5767    #
5768    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
5769
5770    if "alias" in exp.arg_types and not isinstance(exp, Window):
5771        exp.set("alias", alias)
5772        return exp
5773    return Alias(this=exp, alias=alias)
5774
5775
5776def subquery(
5777    expression: ExpOrStr,
5778    alias: t.Optional[Identifier | str] = None,
5779    dialect: DialectType = None,
5780    **opts,
5781) -> Select:
5782    """
5783    Build a subquery expression.
5784
5785    Example:
5786        >>> subquery('select x from tbl', 'bar').select('x').sql()
5787        'SELECT x FROM (SELECT x FROM tbl) AS bar'
5788
5789    Args:
5790        expression: the SQL code strings to parse.
5791            If an Expression instance is passed, this is used as-is.
5792        alias: the alias name to use.
5793        dialect: the dialect used to parse the input expression.
5794        **opts: other options to use to parse the input expressions.
5795
5796    Returns:
5797        A new Select instance with the subquery expression included.
5798    """
5799
5800    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
5801    return Select().from_(expression, dialect=dialect, **opts)
5802
5803
5804def column(
5805    col: str | Identifier,
5806    table: t.Optional[str | Identifier] = None,
5807    db: t.Optional[str | Identifier] = None,
5808    catalog: t.Optional[str | Identifier] = None,
5809    quoted: t.Optional[bool] = None,
5810) -> Column:
5811    """
5812    Build a Column.
5813
5814    Args:
5815        col: Column name.
5816        table: Table name.
5817        db: Database name.
5818        catalog: Catalog name.
5819        quoted: Whether to force quotes on the column's identifiers.
5820
5821    Returns:
5822        The new Column instance.
5823    """
5824    return Column(
5825        this=to_identifier(col, quoted=quoted),
5826        table=to_identifier(table, quoted=quoted),
5827        db=to_identifier(db, quoted=quoted),
5828        catalog=to_identifier(catalog, quoted=quoted),
5829    )
5830
5831
5832def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
5833    """Cast an expression to a data type.
5834
5835    Example:
5836        >>> cast('x + 1', 'int').sql()
5837        'CAST(x + 1 AS INT)'
5838
5839    Args:
5840        expression: The expression to cast.
5841        to: The datatype to cast to.
5842
5843    Returns:
5844        The new Cast instance.
5845    """
5846    expression = maybe_parse(expression, **opts)
5847    return Cast(this=expression, to=DataType.build(to, **opts))
5848
5849
5850def table_(
5851    table: Identifier | str,
5852    db: t.Optional[Identifier | str] = None,
5853    catalog: t.Optional[Identifier | str] = None,
5854    quoted: t.Optional[bool] = None,
5855    alias: t.Optional[Identifier | str] = None,
5856) -> Table:
5857    """Build a Table.
5858
5859    Args:
5860        table: Table name.
5861        db: Database name.
5862        catalog: Catalog name.
5863        quote: Whether to force quotes on the table's identifiers.
5864        alias: Table's alias.
5865
5866    Returns:
5867        The new Table instance.
5868    """
5869    return Table(
5870        this=to_identifier(table, quoted=quoted),
5871        db=to_identifier(db, quoted=quoted),
5872        catalog=to_identifier(catalog, quoted=quoted),
5873        alias=TableAlias(this=to_identifier(alias)) if alias else None,
5874    )
5875
5876
5877def values(
5878    values: t.Iterable[t.Tuple[t.Any, ...]],
5879    alias: t.Optional[str] = None,
5880    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
5881) -> Values:
5882    """Build VALUES statement.
5883
5884    Example:
5885        >>> values([(1, '2')]).sql()
5886        "VALUES (1, '2')"
5887
5888    Args:
5889        values: values statements that will be converted to SQL
5890        alias: optional alias
5891        columns: Optional list of ordered column names or ordered dictionary of column names to types.
5892         If either are provided then an alias is also required.
5893
5894    Returns:
5895        Values: the Values expression object
5896    """
5897    if columns and not alias:
5898        raise ValueError("Alias is required when providing columns")
5899
5900    return Values(
5901        expressions=[convert(tup) for tup in values],
5902        alias=(
5903            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
5904            if columns
5905            else (TableAlias(this=to_identifier(alias)) if alias else None)
5906        ),
5907    )
5908
5909
5910def var(name: t.Optional[ExpOrStr]) -> Var:
5911    """Build a SQL variable.
5912
5913    Example:
5914        >>> repr(var('x'))
5915        '(VAR this: x)'
5916
5917        >>> repr(var(column('x', table='y')))
5918        '(VAR this: x)'
5919
5920    Args:
5921        name: The name of the var or an expression who's name will become the var.
5922
5923    Returns:
5924        The new variable node.
5925    """
5926    if not name:
5927        raise ValueError("Cannot convert empty name into var.")
5928
5929    if isinstance(name, Expression):
5930        name = name.name
5931    return Var(this=name)
5932
5933
5934def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
5935    """Build ALTER TABLE... RENAME... expression
5936
5937    Args:
5938        old_name: The old name of the table
5939        new_name: The new name of the table
5940
5941    Returns:
5942        Alter table expression
5943    """
5944    old_table = to_table(old_name)
5945    new_table = to_table(new_name)
5946    return AlterTable(
5947        this=old_table,
5948        actions=[
5949            RenameTable(this=new_table),
5950        ],
5951    )
5952
5953
5954def convert(value: t.Any, copy: bool = False) -> Expression:
5955    """Convert a python value into an expression object.
5956
5957    Raises an error if a conversion is not possible.
5958
5959    Args:
5960        value: A python object.
5961        copy: Whether or not to copy `value` (only applies to Expressions and collections).
5962
5963    Returns:
5964        Expression: the equivalent expression object.
5965    """
5966    if isinstance(value, Expression):
5967        return maybe_copy(value, copy)
5968    if isinstance(value, str):
5969        return Literal.string(value)
5970    if isinstance(value, bool):
5971        return Boolean(this=value)
5972    if value is None or (isinstance(value, float) and math.isnan(value)):
5973        return NULL
5974    if isinstance(value, numbers.Number):
5975        return Literal.number(value)
5976    if isinstance(value, datetime.datetime):
5977        datetime_literal = Literal.string(
5978            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
5979        )
5980        return TimeStrToTime(this=datetime_literal)
5981    if isinstance(value, datetime.date):
5982        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
5983        return DateStrToDate(this=date_literal)
5984    if isinstance(value, tuple):
5985        return Tuple(expressions=[convert(v, copy=copy) for v in value])
5986    if isinstance(value, list):
5987        return Array(expressions=[convert(v, copy=copy) for v in value])
5988    if isinstance(value, dict):
5989        return Map(
5990            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
5991            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
5992        )
5993    raise ValueError(f"Cannot convert {value}")
5994
5995
5996def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
5997    """
5998    Replace children of an expression with the result of a lambda fun(child) -> exp.
5999    """
6000    for k, v in expression.args.items():
6001        is_list_arg = type(v) is list
6002
6003        child_nodes = v if is_list_arg else [v]
6004        new_child_nodes = []
6005
6006        for cn in child_nodes:
6007            if isinstance(cn, Expression):
6008                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6009                    new_child_nodes.append(child_node)
6010                    child_node.parent = expression
6011                    child_node.arg_key = k
6012            else:
6013                new_child_nodes.append(cn)
6014
6015        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6016
6017
6018def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6019    """
6020    Return all table names referenced through columns in an expression.
6021
6022    Example:
6023        >>> import sqlglot
6024        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6025        ['a', 'c']
6026
6027    Args:
6028        expression: expression to find table names.
6029        exclude: a table name to exclude
6030
6031    Returns:
6032        A list of unique names.
6033    """
6034    return {
6035        table
6036        for table in (column.table for column in expression.find_all(Column))
6037        if table and table != exclude
6038    }
6039
6040
6041def table_name(table: Table | str, dialect: DialectType = None) -> str:
6042    """Get the full name of a table as a string.
6043
6044    Args:
6045        table: Table expression node or string.
6046        dialect: The dialect to generate the table name for.
6047
6048    Examples:
6049        >>> from sqlglot import exp, parse_one
6050        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6051        'a.b.c'
6052
6053    Returns:
6054        The table name.
6055    """
6056
6057    table = maybe_parse(table, into=Table)
6058
6059    if not table:
6060        raise ValueError(f"Cannot parse {table}")
6061
6062    return ".".join(
6063        part.sql(dialect=dialect, identify=True)
6064        if not SAFE_IDENTIFIER_RE.match(part.name)
6065        else part.name
6066        for part in table.parts
6067    )
6068
6069
6070def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6071    """Replace all tables in expression according to the mapping.
6072
6073    Args:
6074        expression: expression node to be transformed and replaced.
6075        mapping: mapping of table names.
6076        copy: whether or not to copy the expression.
6077
6078    Examples:
6079        >>> from sqlglot import exp, parse_one
6080        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6081        'SELECT * FROM c'
6082
6083    Returns:
6084        The mapped expression.
6085    """
6086
6087    def _replace_tables(node: Expression) -> Expression:
6088        if isinstance(node, Table):
6089            new_name = mapping.get(table_name(node))
6090            if new_name:
6091                return to_table(
6092                    new_name,
6093                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6094                )
6095        return node
6096
6097    return expression.transform(_replace_tables, copy=copy)
6098
6099
6100def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6101    """Replace placeholders in an expression.
6102
6103    Args:
6104        expression: expression node to be transformed and replaced.
6105        args: positional names that will substitute unnamed placeholders in the given order.
6106        kwargs: keyword arguments that will substitute named placeholders.
6107
6108    Examples:
6109        >>> from sqlglot import exp, parse_one
6110        >>> replace_placeholders(
6111        ...     parse_one("select * from :tbl where ? = ?"),
6112        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6113        ... ).sql()
6114        "SELECT * FROM foo WHERE str_col = 'b'"
6115
6116    Returns:
6117        The mapped expression.
6118    """
6119
6120    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6121        if isinstance(node, Placeholder):
6122            if node.name:
6123                new_name = kwargs.get(node.name)
6124                if new_name:
6125                    return convert(new_name)
6126            else:
6127                try:
6128                    return convert(next(args))
6129                except StopIteration:
6130                    pass
6131        return node
6132
6133    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6134
6135
6136def expand(
6137    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6138) -> Expression:
6139    """Transforms an expression by expanding all referenced sources into subqueries.
6140
6141    Examples:
6142        >>> from sqlglot import parse_one
6143        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6144        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6145
6146        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6147        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6148
6149    Args:
6150        expression: The expression to expand.
6151        sources: A dictionary of name to Subqueryables.
6152        copy: Whether or not to copy the expression during transformation. Defaults to True.
6153
6154    Returns:
6155        The transformed expression.
6156    """
6157
6158    def _expand(node: Expression):
6159        if isinstance(node, Table):
6160            name = table_name(node)
6161            source = sources.get(name)
6162            if source:
6163                subquery = source.subquery(node.alias or name)
6164                subquery.comments = [f"source: {name}"]
6165                return subquery.transform(_expand, copy=False)
6166        return node
6167
6168    return expression.transform(_expand, copy=copy)
6169
6170
6171def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6172    """
6173    Returns a Func expression.
6174
6175    Examples:
6176        >>> func("abs", 5).sql()
6177        'ABS(5)'
6178
6179        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6180        'CAST(5 AS DOUBLE)'
6181
6182    Args:
6183        name: the name of the function to build.
6184        args: the args used to instantiate the function of interest.
6185        dialect: the source dialect.
6186        kwargs: the kwargs used to instantiate the function of interest.
6187
6188    Note:
6189        The arguments `args` and `kwargs` are mutually exclusive.
6190
6191    Returns:
6192        An instance of the function of interest, or an anonymous function, if `name` doesn't
6193        correspond to an existing `sqlglot.expressions.Func` class.
6194    """
6195    if args and kwargs:
6196        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6197
6198    from sqlglot.dialects.dialect import Dialect
6199
6200    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6201    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6202
6203    parser = Dialect.get_or_raise(dialect)().parser()
6204    from_args_list = parser.FUNCTIONS.get(name.upper())
6205
6206    if from_args_list:
6207        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6208    else:
6209        kwargs = kwargs or {"expressions": converted}
6210        function = Anonymous(this=name, **kwargs)
6211
6212    for error_message in function.error_messages(converted):
6213        raise ValueError(error_message)
6214
6215    return function
6216
6217
6218def true() -> Boolean:
6219    """
6220    Returns a true Boolean expression.
6221    """
6222    return Boolean(this=True)
6223
6224
6225def false() -> Boolean:
6226    """
6227    Returns a false Boolean expression.
6228    """
6229    return Boolean(this=False)
6230
6231
6232def null() -> Null:
6233    """
6234    Returns a Null expression.
6235    """
6236    return Null()
6237
6238
6239# TODO: deprecate this
6240TRUE = Boolean(this=True)
6241FALSE = Boolean(this=False)
6242NULL = Null()